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 p.name, permiss.permission_name,permiss.state_desc,  princ.name
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 princ.name = ‘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
(
select  p.name
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 princ.name = ‘Domain\User’
WHERE permiss.state_desc = ‘GRANT’
  group by p.name
)
SELECT p.name
FROM
sys.procedures p
left join ctePROCS procs on p.name = procs.name
where procs.name is null

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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: