Before we get into auditing SQL Server permissions a reminder of a few definitions might be helpful.
In SQL Server anything that can be granted a right to perform an activity is called a principal. So fundamentally principals are logins, users, roles, etc. Principals can also be separated into server principals and database principals. These reflect the scope of the principal. See (http://msdn.microsoft.com/en-us/library/ms181127(v=sql.90).aspx for further details).
Permissions are what allow principals to perform these activities in SQL Server. Depending on the scope, the permission granted is either a server permission or a database permission.
Roles are used to group together permissions or other roles. They are a means of facilitating the granting of multiple permissions or roles to users.
SQL Server provides some predefined roles to help in database administration.
Fixed Server Roles (see http://msdn.microsoft.com/en-us/library/ms175892(v=sql.90)) are defined at the server level and have server level permissions. They cannot be added, removed, or changed. Each member of a fixed server role can add other users to the role. A more detailed description of Fixed Server Roles can be seen at http://www.mssqltips.com/sqlservertip/1887/understanding-sql-server-fixed-server-roles/
Fixed Database Roles (see http://msdn.microsoft.com/en-us/library/ms189612(v=sql.90)) are defined at the database level and exist in each database. Members of the db_owner and db_securityadmin database roles can manage fixed database role membership; however, only members of the db_owner database role can add members to the db_owner fixed database role. A more detailed description of fixed database roles can be seen at http://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/
User Defined (or Flexible) Database Roles allow you to create your own roles. After you create a role you can configure the database permissions of the role by using grant, deny, and revoke etc.
From SQL Server 2012 you can also create User Defined Server Roles (see http://sequelinfo.wordpress.com/2012/02/25/sql-server-2012-user-defined-server-roles/)
To get a list of Fixed Server Roles use the sys.server_principals view (see http://msdn.microsoft.com/en-us/library/ms188786(v=sql.90) )
Spool the view (and all subsequent views mentioned in this post) to a CSV type file by following the instructions in the following document http://www.isaca.org/Groups/Professional-English/sql-servers/GroupDocuments/SQL%20Server%20-%20Output%20to%20CSV.doc
and substituting in the following SQL
We will also need the users that we previously imported (see http://www.isaca.org/Blogs/273340/Lists/Posts/ViewPost.aspx?ID=13)
The users that have been allocated Fixed Server Roles can be seen in the sys.server_role_members view (see http://msdn.microsoft.com/en-us/library/ms190331(v=sql.90).aspx)
Once each of the above are in your CAATs tool you can use the server_role_members view to join to roles (using the “Server Principal ID of the role” and users (using “Server Principal ID of the member”). It is then a matter of reviewing the allocated roles for appropriateness. Special attention should be paid to the SYSADMIN and PUBLIC Fixed Server Roles.
We can also view a list of individual server permissions using the sys.server_permissions view (see http://msdn.microsoft.com/en-US/library/ms186260(v=sql.90).aspx
As above this can be joined to users (using “grantee principal id” and “grantor principal id”). This will then list all server permissions with the grantee and grantor details. This should again be reviewed for appropriateness.
To get a list of Database Roles (both fixed and user defined) use the sys.database_principals view (see http://msdn.microsoft.com/en-us/library/ms187328(v=sql.90).aspx)
For each database under review
USE <database name>
(Note the column IS_FIXED_ROLE=1 for Fixed Server Roles)
Get a list of the database users and roles
Get a list of the role members from the view database_role_members (see http://msdn.microsoft.com/en-US/library/ms189780(v=sql.90).aspx)
Once each of the above are in your CAATs tool you can use the database_role_members view to join to roles (using the “Database Principal ID of the role” and users (using “Database Principal ID of the member”). It is then a matter of reviewing the allocated roles for appropriateness. Note that PUBLIC is a database role, while GUEST is a database user. Also don’t forget to review for adequate separation of duties.
We can also view a list of individual database permissions using the sys.database_permissions view (see http://msdn.microsoft.com/en-us/library/ms188367(v=sql.90) )
In your CAATs tool you can use the database_principals view to see who has been granted these permissions (join using the “Grantee_principal_id)”. Note that negative numbers are system objects.
It is also possible to see what objects permissions have been granted on using the sys.objects view
And join on major_id from database_permissions with object_id
System objects can be further broken down in other views if required e.g. sys.tables, sys.triggers, sys.procedures.
The company you are auditing should have a policy on what permissions may be granted to users. If so import them to your CAATs tool (using the method above from a “model” database that has the correct permissions set). Compare the two databases, questioning any differences with the DBA.
One of the most important (in my opinion) SQL Server security areas to consider is “Application Roles”. If your application is defined without a proxy user, that is, all the application users are also defined as individual users on the SQL Server database, it may be possible for them to access the database via ODBC.
This means that they could use common tools such as Microsoft Access or Microsoft Excel to access the database directly. They are now connected to the database with the permissions defined for them but critically any controls defined by the application can be bypassed. For example, if your application is written in Visual Basic and ensures that a particular field has particular limits this may be overridden by updating the database table directly.
Further information on Application Roles may be seen at http://msdn.microsoft.com/en-us/library/ms190998(v=sql.90).aspx
Fundamentally, an application role is a role that is only enabled by an application executing the set application role stored procedure with a password known only to the application.
To check if Application Roles are in use
And discuss the roles with the system administrator