There are times when you need to audit user permissions to ensure that things aren’t getting inserted, over-written, or deleted. I looked around and found this script which uses several sys objects, which gives us most of the information we need.

select dp.NAME AS principal_name
     ,dp.type_desc AS principal_type_desc
     ,o.NAME AS object_name
     ,o.type_desc as Object_type
     ,p.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o
     on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp
     on p.grantee_principal_id = dp.principal_id
order by o.NAME

Here is the link where I found the code to show all permissions in SQL server 2005, but the code works just fine in SQL Server 2008.


Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter