Developing Matt

My Technical Journal

User has grant access to which procedures?

leave a comment »

How to get the list of objects (stored procedures, specifically) for which a user has access

SELECT, permiss.permission_name,permiss.state_desc,
FROM sys.database_permissions permiss
JOIN sys.database_principals princ
        ON permiss.grantee_principal_id = princ.principal_id
JOIN sys.procedures p
        ON major_id = p.object_id AND = ‘Domain\UserName’
where permiss.state_desc = ‘GRANT’

And here’s how to get the list of objects for which a user doesn’t have access.  It is simply turning the above on it’s head.  Lots of ways to do it, but I kept it this way for readability

with ctePROCS as
from sys.database_permissions permiss
join sys.database_principals princ
     on permiss.grantee_principal_id = princ.principal_id
join sys.procedures p on   major_id = p.object_id
  and = ‘Domain\User’
WHERE permiss.state_desc = ‘GRANT’
  group by
sys.procedures p
left join ctePROCS procs on =
where is null


Written by matt

November 20, 2009 at 10:29 am

Posted in T-Sql

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: