Need Access Details

Question

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.

Answer 1

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.

Answer 2

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.