:::: MENU ::::

Get permissions for SQL Server Database users

  • Mar 04 / 2009
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Get permissions for SQL Server Database users

As a part of best practices implementation, some times i require to review and audit the rights of database users in a specific database. I have created following script to get list of permissions for all database users in a database. This script will work for SQL Server 2005 and onwards. I will run it on AdventureWorks

 
Use AdventureWorks  
 GO  
 SELECT USER_NAME(grantee_principal_id) AS [UserName],  
 OBJECT_NAME(major_id) AS ObjectName,  
 permission_name AS PermissionName,  
 state_desc AS PermissionStatus  
 FROM SYS.DATABASE_PERMISSIONS  
 WHERE class = 1  
 ORDER BY userName,permissionstatus  
 GO  

The result will be produced as shown a part in following snap.

Permissions for DB Users

It is important to mention that in case of permissions Column-level permissions override object-level permissions on the same entity.

Consult us to explore the Databases. Contact us