2

I have a novice question: I could see different (presumably) AD Groups and AD accounts under the SQL Server Server Logins. However, I am unable to tell whether some of these users are AD users vs AD Groups. Someone mentioned you could look at the LOGO and if it's a multiple person it's a group, single person is a user. Is this the best way to tell? That doesn't seem reliable.

enter image description here

Also, how can I see the USERS within an AD group? I can see the group, but I need to see the users. I do NOT have access to the AD Server.

Thank you so much.

0
3

Yes the icon changes if it's a group vs a user. You can also query sys.server_principals.

To see the users within an AD group, you can use xp_logininfo. As long as the AD group is a login on the SQL Server, you can query it using xp_logininfo. Here's a really old blog post I wrote on it.

Syntax:

xp_logininfo [ [ @acctname = ] 'account_name' ] 
     [ , [ @option = ] 'all' | 'members' ] 
     [ , [ @privilege = ] variable_name OUTPUT]

Example:

EXEC master.dbo.xp_logininfo 'DomainName\GroupName', 'members'
3
  • I tried running the stored procedure this way (my group name has spaces, for some reason). I chose members, but then commented later to see if it fixes the issue. I recieved this error: EXEC master.dbo.xp_logininfo 'ACCOUNTDOMAIN\Group Name Has Spaces' --, 'members' "Msg 15404, Level 16, State 11, Procedure xp_logininfo, Line 64 Could not obtain information about Windows NT group/user 'ACCOUNTDOMANIN... error code 0x534" Aug 23 '16 at 20:37
  • @LearnByReading, looks like you've got a typo in your AD group name as per the error message. But according to your screen shot it should be ACCOUNTS\GroupNameGoesHere. You could try using square brackets to deal with the spaces or double quotes. I don't have an example here to test out which way will work.
    – Tara Kizer
    Aug 23 '16 at 20:56
  • my new question dba.stackexchange.com/questions/154303/… is related to your answer. Please reply if you can! THank you! Nov 4 '16 at 18:39
2

The logo is reliable to the best of my knowledge. Probably the easiest way though is to look at sys.server_principals or sys.database_principals and look at the type_desc column.

enter image description here

2

This query should help you.

select name,  type_desc, is_disabled 
from sys.server_principals

[Type_desc] have values of

ASYMMETRIC_KEY_MAPPED_LOGIN
CERTIFICATE_MAPPED_LOGIN
SERVER_ROLE
SQL_LOGIN
WINDOWS_GROUP
WINDOWS_LOGIN
2

If you are on the domain and have read access (most do) you can run some PowerShell commands or simple DOS commands. You will need the PowerShell module for PS commands.

In DOS command-window:

NET GROUP [groupname] /DOMAIN

From PowerShell:

Import-Module ActiveDirectory;
Get-ADGroupMember -Identity [groupname] | SELECT name;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.