AD Groups as logins

Question

Good Morning,

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?

Answer 1

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

Answer 2

You can use sys.database_principals and 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.

Answer 3

Hi @Chaitanya Kiran ,

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.
step1

step2

Best regards,
Seeya

---

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.