Who has access to what in my databases in SQL Server?
Have you ever found yourself asking this question? I know I have...many times. Usually about the time auditors come in wanting reports. Or, when someone leaves the company and you need to remove their access.
In this tutorial, I will show some very basic queries on how to query SQL Server databases for users and the permissions those users are assigned to various objects in the database. For this article, I created a SQL Server Login called ziggy.trainer. The login is also mapped to a database user called ziggy.trainer. I will be reviewing the permissions ziggy has in the AdventureWorks2012 database.
To query for database level permissions I will be working with 3 Security Catalog Views (sys.database_principals, sys.database_role_members , sys.database_permissions) and an Object Catalog View (sys.objects). Using these views in combination with each other, I can see who has access to a specific database and any objects within that database.
Here is a brief description of these views.
Security Catalog Views
These views return security information on SQL Server securables. A securable is anything in SQL Server that can be granted access to or denied access.
sys.database_principals
Principals in SQL Server are the entities that receive the permissions to a securable. There are windows-level, server-level and database-level principals.
- Server level principals would be the SQL Server login
- Database level principals include the database user, database role and the application role
This view returns a row for each principal in the database. In other words, a row for each user, group or role that has permissions for that particular database. SQL Server 2012 & 2014 has some new columns, authentication_type, authentication_type_desc, default_language_name and default_language_lcid
sys.database_role_members
This DMV returns one row for each member in a database role. Basically, a view to identify principals that belong to a particular role in the database. A role is similar to a group, it contains members or principals and you can assign the role specific permissions. For example, db_datareader is a fixed database-level role that has access to read all user tables in the database. Querying this view with the sys.database_principals view, I can see a list of users (principals) who are members of the db_datareader role.
Here is an example of that query
sys.database_permissions
This view returns a row for every permission assigned to a securable in the database. Querying the view will show a list of permissions, such as Select, Update or Delete and whether or not that permission is granted, denied or revoked for a securable.
Object Catalog Views
These views return information used by the SQL Server Database Engine. Specifically, the Object Catalog views will return metadata on the securables in SQL Server.
sys.objects
This view will return a row for each user object created within the database.
Querying sys.database_principals alone I can get a list of the principals (users, group or roles) that have been given access to the database. From the table below you can see some of the metadata for each user, group or role. For the purposes of this article we are just concerned with the name, principal_id and type.
Joining sys.database_principals and sys.database_role_members to each other will tell me the roles that the users are assigned. In this query I am joining the principal_id in sys.database_principals to the member_principal_id column in sys.database_role_members to get the members of the roles in the AdventureWorks2012 database.
Executing this query, the results below, show me that the domain user is in the db_datareader and the db_datawriter roles. Specifically, the domain user can read and write to all the user-defined tables in the database.
**Please note the new authentication_type_desc column. This new column in SQL Server 2012 and 2014 now shows the authentication type of the user. In this case I can see ziggy.trainer was authenticated through the SQL Server instance as opposed to the domain user that was authenticated by Windows.
Using the principal_id from the previous query, I can query the sys.database_permissions view to get all the permissions assigned to that user or principal. If I take it a step further and join to sys.objects, I can get the names and types of the objects.
Looking at the results of this query, you can see the permissions ziggy.trainer is assigned for specific objects in the AdventureWorks2012 database. The state_desc column tells me whether or not the user was granted or denied access to the object. The permission_name column tells me the permission the user was given. In this example, we can see that ziggy.trainer was granted the execute permission on three stored procedures, granted delete permissions on a couple of tables and finally granted the select permission on other tables and views.
Instead of querying by principal_id, I can change the query to return a list of all of the stored procedures (P) , user tables (U)and views(V) by changing the where clause to look for these types in the sys.objects table. You can go to the MSDN article ( http://technet.microsoft.com/en-us/library/ms190324(v=sql.110).aspx ) to view the complete list of object types to retrieve.
Looking at the results below, I can quickly see which users have the execute permission to the stored procedures in the database.
Simple changes to the Where clause can also allow me to query for specific permissions, such as Deletes (DL), Updates (UP) or even Alter(AL) permissions. This query will return all the users who have the Delete or Update permissions.
Finally, if I wanted to get a list of all the objects in a database along with the permissions assigned to the object, I can change the query to a Left Outer Join between sys.objects and sys.database_permissions. The Left Outer Join will give me all rows in sys.objects (the left side) regardless of a permission listed in the sys.database_permissions view. If there are no persmissions assigned to the object, it will post “No Permissions” under the Permission column.
The results show that the stored procedures uspLogError and uspPrintError have no permissions assigned as opposed to the uspUpdateEmployeeHireInfo procedure has Execute and View Definition assigned.
These are pretty basic queries, but they can give a DBA a great deal of information on who is accessing the database(s), how they are accessing the database(s) and what can they do in the database(s).
