question

ChaitanyaKiran-2787 avatar image
0 Votes"
ChaitanyaKiran-2787 asked Cathyji-msft answered

Need Access Details

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
1 Vote"
Cathyji-msft answered

Hi @ChaitanyaKiran-2787,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
1 Vote"
cooldadtx answered cooldadtx commented

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The group is member of some user defined role. I want to know what all permissions this role has.

0 Votes 0 ·
cooldadtx avatar image cooldadtx ChaitanyaKiran-2787 ·

User defined role as in a custom server role, database role or application role? Pre-defined server roles are in the docs. Database/application roles are under the Security tab of the ddatabase itself. Under the Secureables it shows you the permissions.

131956-image.png

What information is SSMS not showing you that you think you should be seeing here?


0 Votes 0 ·
image.png (44.5 KiB)

131986-image.png



I see it empty

0 Votes 0 ·
image.png (24.3 KiB)
Show more comments