SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Maxer
Maxer
Say Hey Kid
Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)

Group: General Forum Members
Points: 672 Visits: 1603
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!
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (10K reputation)

Group: Moderators
Points: 10254 Visits: 1917
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
Maxer
Maxer
Say Hey Kid
Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)

Group: General Forum Members
Points: 672 Visits: 1603
Thanks!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search