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) )
-- Cursor to hold database names to be backed up
DECLARE
Get_Groups CURSOR
FOR Select
name
from master..syslogins
where
isntgroup
= 1 and status > 9 or Name= 'BUILTIN\ADMINISTRATORS'
-- Open cursor and loop through database names
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;
GO
Update
[tempdb].[dbo].[RESULT_STRING] Set Server = CONVERT(varchar(100), SERVERPROPERTY('Servername'))
Select
* from [tempdb].[dbo].[RESULT_STRING]
SET
NoCount OFF