Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Want to view who has permissions to execute sp_notify_operator - how? Expand / Collapse
Author
Message
Posted Wednesday, January 12, 2011 9:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 288, Visits: 1,175
So in SSMS I can't just right click on a system stored procedure (msdb database) and view properties on it.

So if I want to grant additional permissions I have to use GRANT to do that.

However, I first want to view everyone who already has permissions for that system sproc.


But how do I go about doing that?

I can select from [msdb].[sys].[database_permissions], but then I need a lookup to figure out what the object is, what would I join the major_id field on? That is what links back to the actual object being described, right?

sys.database_principals is who the users actually are so:
sys.database_principals INNER JOIN sys.database_permissions
ON sys.database_principals.principal_id = sys.database_permissions.grantee_principal_id

Works to get me names of users, but I still don't see how to get the name of the object that the user has permissions to?

Thanks!

Post #1046598
Posted Thursday, January 13, 2011 6:44 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
The major_id field varies depending on the securable being referenced. Probably the easiest way to handle this is to use the OBJECT_NAME() function like I did here:

Auditing SQL Server User and Role Permissions for Databases

Otherwise you can join it to the sys.objects table's object_id like I did here:

Retrieving SQL Server Permissions for Disaster Recovery


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1047225
Posted Thursday, January 13, 2011 6:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 288, Visits: 1,175
Thanks!
Post #1047235
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse