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: Tuesday, July 22, 2014 1:24 PM
Points: 272, Visits: 1,081
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: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
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: Tuesday, July 22, 2014 1:24 PM
Points: 272, Visits: 1,081
Thanks!
Post #1047235
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse