AD Groups as logins

Chaitanya Kiran 616 Reputation points
2021-09-24T15:56:17.77+00:00

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
    2021-09-24T16:20:29.213+00:00

    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
    2021-09-24T21:46:49.423+00:00

    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.


  3. Seeya Xi-MSFT 16,246 Reputation points
    2021-09-27T06:12:28.017+00:00

    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
    135473-2.png
    step2
    135358-1.png

    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.

    0 comments No comments