What does Mark Twain have to do with SQL Server?

If the atoms that comprised his living flesh did not succumb to entropy he most probably would have been a SQL user and would have had a Windows domain account somewhere.  Really, I just used Twain to tie AD and SQL for the phrase, "...never the Twain shall meet". 

However, in truth , SQL Server and Windows domain accounts do meet, often. But...if that windows domain account is introducing itself to SQL via a Windows group membership, DBA's may find it difficult to dig a direct ditch to the user. 

Take for example, the default BUILTIN\Administrators group.

Who is in this group? How can I, as a lowly DBA, tell?

With most tasks, there are many ways. LDAP queries, xp_cmdshell to DSQUERY perhaps...

Follows a query I use that takes advantage of a stored procedure that incorrectly identifies itself as extended with "xp_".

I am referring to xp_logininfo. If you run:

xp_logininfo 'BUILTIN\Administrators','members'

AND you have rights to so such a thing, you will see the members of this group. And it follows that you can do the same for any group.

What xp_logininfo does not show is the server name of the SQL Server on which the query is run. This is important to me when building a full repository of the SQL Server infrastructure. Below is a query that I have used in SSIS to build a table with the output of xp_logininfo that includes the server name.  The query can be run independently through SSMS for kicks. You might be surprised at what you find. The code is not ideally formatted. I am awaiting purchase of RedGate's SQL Refactor to do the code up right.  Damn expired trial and a ditch diggers salary.

The following query gets all Windows groups on the server and all its members, which might otherwise be invisible to SQL.

SET NoCount ON

SET quoted_identifier OFF

DECLARE @groupname VARCHAR(100)

IF EXISTS

(SELECT * FROM tempdb.dbo.sysobjects

WHERE id = OBJECT_ID(N'[tempdb].[dbo].[RESULT_STRING]'))

DROP TABLE [tempdb].[dbo].[RESULT_STRING];

CREATE TABLE [tempdb].[dbo].[RESULT_STRING] ( Account_Name VARCHAR(2500),

type varchar(10),

Privilege varchar(10),

Mapped_Login_Name varchar(60),

Group_Name varchar(100) )

DECLARE Get_Groups CURSOR

FOR Select

name from master..syslogins

where

isntgroup = 1 and status > 9 or Name= 'BUILTIN\ADMINISTRATORS'

OPEN Get_Groups

FETCH NEXT FROM Get_Groups INTO @groupname

WHILE ( @@fetch_status <> -1 )

BEGIN

IF ( @@fetch_status = -2 )

BEGIN

FETCH NEXT FROM Get_Groups INTO @groupname

CONTINUE

END

Insert into [tempdb].[dbo].[RESULT_STRING]

Exec master..xp_logininfo @Groupname, 'members'

FETCH NEXT FROM Get_groups INTO @groupname

END

DEALLOCATE Get_Groups

Alter TABLE [tempdb].[dbo].[RESULT_STRING] Add Server varchar(100) NULL;

Update [tempdb].[dbo].[RESULT_STRING] Set Server = CONVERT(varchar(100), SERVERPROPERTY('Servername'))

Select * from [tempdb].[dbo].[RESULT_STRING]

SET NoCount OFF