Find all sprocs that a user cannot execute

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find all sprocs that a user cannot execute

Author

Topic

sixside
Starting Member

12 Posts

Posted - 2011-05-12 : 22:15:06

How can use T-SQL to find all stored procedures (non-system sprocs) that a specific user does NOT have Execute privileges on?

Thanks

latch
Yak Posting Veteran

62 Posts

Posted - 2011-05-13 : 10:54:03

U can try these:

SELECT
dp.Class,
dps1.Name As Grantee,
dps2.Name As Grantor,
so.Name,
so.Type,
dp.Permission_Name,
dp.State_Desc
FROM sys.database_permissions AS dp
JOIN Sys.Database_Principals dps1
ON dp.grantee_Principal_ID = dps1.Principal_ID
JOIN Sys.Database_Principals dps2
ON dp.grantor_Principal_ID = dps2.Principal_ID
JOIN sys.objects AS so
ON dp.major_id = so.object_id
WHERE so.Name = 'UpdateStock'

source:

http://dbaspot.com/sqlserver-programming/462251-find-users-permission-sp.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources