AD Groups as logins

Chaitanya Kiran 616 Reputation points

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,800 questions
{count} votes

3 answers

Sort by: Most helpful
  1. cthivierge 3,981 Reputation points

    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

    0 comments No comments

  2. Erland Sommarskog 78,026 Reputation points MVP

    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 = '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 + '.' +, p.permission_name  
       FROM  sys.objects o  
       JOIN  sys.schemas s ON o.schema_id = s.schema_id  
       CROSS APPLY sys.fn_my_permissions( + '.' +, 'OBJECT') p  

    And then you need to run similar queries for other securable classes to get the full story.

  3. Seeya Xi-MSFT 16,246 Reputation points

    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.

    Best regards,

    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.

    0 comments No comments