April 25, 2014 at 12:43 pm
We have the command for server roles to know the permissions..
sp_srvrolepermission 'bulkadmin'
it will give the permissions list of bulkadmin..in the same way we have any
sp to find the permissions of db roles...?
April 25, 2014 at 3:22 pm
the sys.database_permissions catalog view will give you which permissions are granted. join to the sys.database_principals to filter by role name.
SELECT ob.type_desc, dp.permission_name, dp.state_desc,ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name
FROM sys.database_permissions dp
JOIN sys.database_principals grantee
on dp.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor
on dp.grantor_principal_id = grantor.principal_id
JOIN sys.objects ob
on dp.major_id = ob.object_id
where grantee.name = '<role_name>'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply