Previous DBA created some AD Groups as logins. He added this to some roles. I want to know what permissions the AD Group logins have?
You may have to look in SQL Server Management Studio and connect to the instance.
Look in Security/Logins. Open the group and look in Server Roles and User Mapping
You can use
sys.database_role_members to find out the role membership.
You can use
sys.database_permissions to see what permission that have been granted to a certain principal, for instance:
SELECT perm.* FROM sys.database_permissions perm JOIN sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id WHERE dp.name = 'rolename'
Since a Windows login can be member of many AD groups, it can be quite a bit of work to list the exact permissions for a login. A different approach is to use sys.fm_my_permissions():
SELECT DISTINCT s.name + '.' + o.name, p.permission_name FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id CROSS APPLY sys.fn_my_permissions(s.name + '.' + o.name, 'OBJECT') p
And then you need to run similar queries for other securable classes to get the full story.
Erland uses several key DMVs which works well, so you can try it.
All you need to do is execute it and check the output.
In a UI view of the way, here are the screenshots.
If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
17 people are following this question.