Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Database Permissions

By C.J. Garcia,

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


select
    pr.name as DatabaseRole
  , pm.name as DatabaseUser
  , pm.type_desc as DatabaseUserType
  from
    sys.database_principals pm
  inner join sys.database_role_members dr
  on
    pm.principal_id = dr.member_principal_id
  inner  join sys.database_principals pr
  on
    dr.role_principal_id = pr.principal_id
  where
    pr.name = 'db_datareader'

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.  


select
    pm.name
  , pm.principal_id
  , pr.name as DatabaseRole
  , pm.type
  , pm.type_desc
  , pm.authentication_type_desc
  , pm.default_schema_name
  from
    sys.database_principals pm
  inner join sys.database_role_members dr
  on
    pm.principal_id = dr.member_principal_id
  inner join sys.database_principals pr
  on
    dr.role_principal_id = pr.principal_id
  where
    pm.type in ( 'S', 'U' )
  order by
    principal_id desc

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. 


select
    user_name(grantee_principal_id) as 'User'
  , state_desc
  , permission_name
  , so.name
  , so.type_desc
  from
    sys.database_permissions dp
  inner join sys.objects so
  on
    dp.major_id = so.object_id
  where
    grantee_principal_id = 5
  order by
    type_desc
  , permission_name

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. 


select
    so.name as 'ObjectName'
  , user_name(grantee_principal_id)
  , state_desc as 'Permission State'
  , permission_name as 'Permission'
  , so.type_desc as 'ObjectType'
  from
    sys.database_permissions dp
  inner join sys.objects so
  on
    dp.major_id = so.object_id
  where
    so.type in ( 'P', 'U', 'V' )
  order by
    so.type

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.


select
    so.name as 'ObjectName'
  , user_name(grantee_principal_id) as 'User'
  , state_desc as 'Permission State'
  , permission_name as 'Permission'
  , so.type_desc as 'ObjectType'
  from
    sys.database_permissions dp
  inner join sys.objects so
  on
    dp.major_id = so.object_id
  where
    dp.type = 'DL'
    or dp.type = 'UP'
  order by
    ObjectName

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.


select distinct
    so.type_desc as 'ObjectType'
  , so.name as 'ObjectName'
  , isnull(state_desc, ' ') + ' ' + isnull(permission_name, 'No Permissions') as 'Permission'
  from
    sys.objects so
  left outer join sys.database_permissions dp
  on
    so.object_id = dp.major_id
  where
    so.type in ( 'P', 'U', 'V' )
  order by
    ObjectType
  , ObjectName

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). 

References

Total article views: 5602 | Views in the last 30 days: 40
 
Related Articles
BLOG

Checking Permissions

Someone posted this query recently: select a.*,name, b.* from sys.database_principals a, sys.datab...

BLOG

TSQL Snippet for viewing basic info on database principals and their permissions

Quick snippet I put together for reviewing basic info on database users/principals, permissions, and...

BLOG

Clean Code is Easier to Read – SQL Prompt

I saw a post recently that had query that looked like this: select a.*,name, b.* from sys.database...

FORUM

Sys.Databases

Creating a trigger on Sys.Databases

BLOG

A Script A Day - Day 5 - Database Owner Permissions

Today's script will list all principals with membership in the db_owner fixed database role. /*...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones