January 12, 2011 at 9:51 am
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
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
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply