January 19, 2016 at 3:39 am
Hello, maybe somebody help me.
Have you got a script, what give a result set of user permision
login, role, procedure, access right
January 19, 2016 at 4:12 am
If you're after server level permissions, query these two views:
sys.server_permissions
sys.server_role_members
If it's database level permissions you need, query these views:
sys.database_permissions
sys.database_role_members
Have a try at that, and please post back if there's anything you don't understand.
John
January 19, 2016 at 4:33 am
Thank you very much for your help , I will try to write a query .
Thanks again for your help.
January 19, 2016 at 5:23 am
Maybe someone needed
SELECT Rls.name as RoleMemeber,RlsMembers.name as [User]
INTO #RoleUser
FROM sys.Database_Principals Rls
INNER JOIN sys.database_role_members drm ON Rls.principal_id = drm.role_principal_id
INNER JOIN sys.Database_Principals RlsMembers ON drm.member_principal_id = RlsMembers.principal_id
WHERE Rls.is_fixed_role = 0
AND Rls.type = 'R'
select permission_name as [permission_name],OBJECT_NAME(major_id) as ObjectName,dp.Name,state_desc
INTO #RoleProcedure
FROM sys.database_permissions perms
INNER JOIN sys.database_principals dp ON perms.grantee_principal_id = dp.principal_id
WHERE Major_id > 0 and grantee_principal_id > 0
AND OBJECT_NAME(major_id) IS NOT NULL
and dp.type = 'R'
ORDER BY state_desc, perms.permission_name, OBJECT_NAME(major_id)
select RoleMemeber,ru.[User],permission_name,ObjectName,state_desc
from #RoleUser ru inner join #RoleProcedure rp on ru.RoleMemeber = rp.Name
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply