Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SQL Server 2005: View all permissions

Today I was asked how one can query the server to list out all permissions that a principal has on all the securables in a SQL Server database. After 10 mins of searching through BOL I came up with the following that seems to work quite well:

    1 select dp.NAME AS principal_name,

    2         dp.type_desc AS principal_type_desc,

    3         o.NAME AS object_name,

    4         p.permission_name,

    5         p.state_desc AS permission_state_desc

    6 from    sys.database_permissions p

    7 left    OUTER JOIN sys.all_objects o

    8 on     p.major_id = o.OBJECT_ID

    9 inner   JOIN sys.database_principals dp

   10 on     p.grantee_principal_id = dp.principal_id

Here's the results:

I'm only putting it here cos I figure this'll be useful one day! Whether its useful to me or someone else is a different matter!! Note that this only works on SQL Server 2005, not on earlier versions.

-Jamie

 

UPDATE: I've posted a newer, better, version of this here: http://blogs.conchango.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx 

Published Friday, February 09, 2007 12:38 AM by jamie.thomson

Comments

 

Jason Haley said:

February 9, 2007 4:20 AM
 

SSIS Junkie said:

Two days ago I posted a blog entry here that showed how the following SQL statement: 1 select dp.NAME

February 10, 2007 1:09 AM
 

Venu said:

this query is very very useful . Thanks a lot

April 17, 2007 11:10 AM
 

Steve said:

The query listed was 95% of what I was looking for. A few tweeks and I should have exactly what I need - thanks!!!!!!

July 12, 2007 6:56 PM
 

jamie.thomson said:

Hi Steve,

What else did you need? If its something useful I might add it to this.

Thanks

Jamie

July 12, 2007 7:26 PM
 

Marianne said:

I love it! THanks

November 15, 2007 1:38 PM
 

John said:

Jamie, thanks heaps

February 27, 2008 2:47 AM
 

Curt said:

I can't speak for Steve, but the one thing I added was the o.TYPE column, so I could sort by object type (view, fn, etc).

Why is it that when I try to view a user's securables in management studio (user properties), it's blank, even though your query brings back results for that user?  Is it something I'm not doing right in Mgmt Studio?

April 7, 2008 10:29 PM
 

vivekananda said:

Hi, I want to write the contents of a varchar variable to an sql file I am using this variable in a stored proc.

So, How to do this?

In simple words, As soon as I execute the stored proc it should create an sql file which contains the contents of that variable.

May 2, 2008 2:08 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems