Need Access Details

Chaitanya Kiran 756 Reputation points
2021-09-14T12:30:58.713+00:00

We have AD Group with members. The AD Group is created as SQL Server login. I would like to know what all access this login has. Please let me know.

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

Accepted answer
  1. CathyJi-MSFT 21,111 Reputation points Microsoft Vendor
    2021-09-15T03:37:53.58+00:00

    Hi @Chaitanya Kiran ,

    Please check below T-SQL.

    --List all effective permissions for securable class server  
    EXECUTE AS LOGIN = 'Cathy'  
    Go  
    SELECT * FROM fn_my_permissions(NULL, 'SERVER');   
    Go  
    
    --List all effective permissions for user Cathy in database AdventureWorks2019  
    EXECUTE AS USER = 'Cathy'  
    GO  
    USE AdventureWorks2019  
    GO  
    SELECT * FROM fn_my_permissions(null, 'database');   
    GO  
    

    Refer to the blog List SQL Server Login and User Permissions to get more detail.


    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Michael Taylor 49,871 Reputation points
    2021-09-14T13:51:33.977+00:00

    Go to SQL Server Management Studio. Go to the server and then Security\Logins and find the group, right click Properties.

    Server Roles indicates the server-level roles the user has. You can look up in the docs what permissions each server role has.
    User Mapping shows the databases the user has access to and what database roles they have for each one. You can look up in the docs what permissions each database role h as.
    Securables shows explicit grants (or denials) of specific permissions at the server level.

    1 person found this answer helpful.