Rudy Rodarte . com

Information

This article was written on 16 Feb 2010, and is filled under SQL Blogs.

Current post is tagged

, ,

View All Permissions for Objects in SQL Server 2008

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.permission_name ,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

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

One Comment

  1. anji
    December 18, 2014

    nice

Leave a Reply