Post reply

Want to view who has permissions to execute sp_notify_operator - how?

  • January 12, 2011 at 9:51 am

    #233130

    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!

  • January 13, 2011 at 6:44 am

    #1272991

    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
    @kbriankelley

  • January 13, 2011 at 6:54 am

    #1272999

    Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply