SQL Server – Query SAM & AD Group members

Background

Last week we had a problem and it went on further than it should have because I did not have access to the local machine.

 

Active Directory & Security Account Management (SAM)

There are two security databases that matter at the OS Level.

On each local machine, we have Security Account Management ( SAM ).

And, on the enterprise level we have Active Directory.

Thankfully, we can query both using xp_loginfo.

 

xp_loginfo

Syntax


declare @privilege varchar(10)

set @privilege  = 'Not wanted'

EXEC xp_logininfo 
		  @acctname = [group-name]'
		, @option = 'members'
		, @privilege =  @privilege output

 

Sample


declare @privilege varchar(10)

set @privilege  = 'Not wanted'

EXEC xp_logininfo 
		  @acctname = 'BUILTIN\Administrators'
		, @option = 'members'
		, @privilege =  @privilege output

Output

xp_loginfo_20161118_0823am-brushed-up

 

Explanation

  1. As we would expect when we query the local BUILTIN\Administrators group
    • We see that the AD Domain Admins groups are listed
  2. Also listed is an additional internal group that I can’t disclose
  3. And, one other local account user; that I can not talk about either

 

Summary

It is a simple query, but it allows me to write a script that queries all of SQL Server Instances.

Upon querying those servers, we can arrange for our internal DBA group to have remote desktop access to the machines.

 

 

Connect Items

  1. Please extend xp_logininfo to be be able to query groups that do not have SQL Server Access
    ID :-  3112040
    Status :- Active
    Type :- Suggestion ( Bug )
    Opened On :- 2016-Nov-18th

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s