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.
It is important to mention that in case of permissions Column-level permissions override object-level permissions on the same entity.