question

ChaitanyaKiran-2787 avatar image
0 Votes"
ChaitanyaKiran-2787 asked SeeyaXi-msft commented

AD Groups as logins

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-general
· 1
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.

Hi @ChaitanyaKiran-2787 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

0 Votes 0 ·
cthivierge avatar image
0 Votes"
cthivierge answered

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

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.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 2
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.

Hi Erland,

Previous DBA also created some roles. How can I know what all permissions this role has?

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

I think that my reply above is good for that.

  1. You need to check role membership, since the role may have been added to other roles, for instance any of the fixed roles.

  2. The second query gives you the exact permissions granted to the role. Then you need to join further on major_id to find which object. Which view to join to depends on the object type.

  3. To try this approach, you can do:

CREATE USER tempuser WITHOUT LOGIN
ALTER ROLE roleyouwanttoinvestigate ADD MEMBER tempuser
EXECUTE AS USER ='tempuser'
go
SELECT ... FROM sys.fn_my_permissions...
go
REVERT

`

You may also find this blog post from Sebastian Meine helpful: https://sqlity.net/en/2584/script-database-permissions/




0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @ChaitanyaKiran-2787,

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.



2.png (28.5 KiB)
1.png (32.0 KiB)
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.