No matter how simple the task or how versed we are with doing a security audit, it seems like we can always stand to learn just a little bit more. No matter how many times we hand an audit report over to the auditor, there is always “just one” more report we have to provide.
I know it seems like I am almost always running some sort of audit report. Whether it is to audit which user may have changed something or what access a user may have overall to the system, there is always a need for more audit.
One of the aspects of an audit that I have not yet written about is with regards to Domain Groups. It is a very common practice to grant access to SQL Server via Domain Group membership. We can easily see which groups have which access, but how often do we try to figure out who is in the group?
When you do try to figure out who is in a Domain Group, do you do like so many DBAs that I know and yell across a cubicle wall to the AD guys and ask them to look it up? Maybe you fire off an email to the AD Admins and ask them to do the grunt work for you. Nobody will shame you for doing either of those two methods. I mean, most people probably toss it over the fence in one way or another.
Fence
It is not a difficult stretch to imagine asking somebody who is an expert at a technology to see if they could get a quick answer to your question for you. That said, it is also really nice when you can service those types of questions by yourself and thus save everybody a little time and effort. Luckily, there is at least one easy way for the SQL Server professional to try and answer questions about group membership.
Microsoft has included a method for the DBA to try and capture information about AD related accounts. This method comes in the form of an extended procedure called xp_logininfo. This procedure can tell me the members of domain groups, the access level the account has, and also validate if the account is still active in AD.
Suppose you needed to determine all of the accounts that may have access to SQL Server, via direct Login or via group membership. In addition, you must find out which of the accounts are no longer valid in AD. I have a script that will do just that by first perusing the groups and then cycling through each of the User accounts produced from the group perusal.
Let’s take a look at that script.
/*
============================================================
Quick script to enumerate Active directory users who get permissions from An Active Directory Group
============================================================
*/--a table variable capturing any errors in the try...catch below
DECLARE @ErrorRecap TABLE
(
ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
, AccountName VARCHAR(256)
, ErrorMessage VARCHAR(256)
);
DECLARE @groupname VARCHAR(256)
, @acctname VARCHAR(256);
IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL
BEGIN
DROP TABLE #tmp;
END
IF OBJECT_ID('tempdb.dbo.#tmpdeeper') IS NOT NULL
BEGIN
DROP TABLE #tmpdeeper;
END
CREATE TABLE [dbo].[#TMP]
(
[ACCOUNTNAME] VARCHAR(256) NULL
, [TYPE] VARCHAR(8) NULL
, [PRIVILEGE] VARCHAR(8) NULL
, [MAPPEDLOGINNAME] VARCHAR(256) NULL
, [PERMISSIONPATH] VARCHAR(256) NULL
);
CREATE TABLE #tmpdeeper
(
[ACCOUNTNAME] VARCHAR(256) NULL
, [TYPE] VARCHAR(8) NULL
, [PRIVILEGE] VARCHAR(8) NULL
, [MAPPEDLOGINNAME] VARCHAR(256) NULL
, [PERMISSIONPATH] VARCHAR(256) NULL
);
DECLARE cgroup CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT name
FROM master.sys.server_principals
WHERE type_desc = 'WINDOWS_GROUP';
OPEN cgroup;
FETCH NEXT FROM cgroup INTO @groupname;
WHILE @@FETCH_STATUS <> -1
BEGIN
BEGIN TRY
INSERT INTO #TMP ( [ACCOUNTNAME]
, [TYPE]
, [PRIVILEGE]
, [MAPPEDLOGINNAME]
, [PERMISSIONPATH] )
EXEC master..xp_logininfo @acctname = @groupname
, @option = 'members';-- show group members
END TRY
BEGIN CATCH
--capture the error details
DECLARE @ErrorSeverity INT
, @ErrorNumber INT
, @ErrorMessage VARCHAR(4000)
, @ErrorState INT;
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorNumber = ERROR_NUMBER();
SET @ErrorMessage = ERROR_MESSAGE();
SET @ErrorState = ERROR_STATE();
--put all the errors in a table together
INSERT INTO @ErrorRecap ( AccountName, ErrorMessage )
SELECT@groupname, @ErrorMessage;
PRINT 'Msg ' + CONVERT(VARCHAR, @ErrorNumber) + ' Level '
+ CONVERT(VARCHAR, @ErrorSeverity) + ' State '
+ CONVERT(VARCHAR, @ErrorState);
PRINT @ErrorMessage;
END CATCH;
FETCH NEXT FROM cgroup INTO @groupname;
END;
CLOSE cgroup;
DEALLOCATE cgroup;
DECLARE cuser CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT DISTINCT ACCOUNTNAME
FROM #TMP
UNION
SELECT sp.name
FROM sys.server_principals sp
WHERE type_desc = 'WINDOWS_LOGIN'; --'WINDOWS_GROUP'
OPEN cuser;
FETCH NEXT FROM cuser INTO @acctname;
WHILE @@FETCH_STATUS <> -1
BEGIN
BEGIN TRY
INSERT INTO #tmpdeeper ( [ACCOUNTNAME]
, [TYPE]
, [PRIVILEGE]
, [MAPPEDLOGINNAME]
, [PERMISSIONPATH] )
EXECUTE master..xp_logininfo @acctname = @acctname,@option = 'all';
END TRY
BEGIN CATCH
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorNumber = ERROR_NUMBER();
SET @ErrorMessage = ERROR_MESSAGE();
SET @ErrorState = ERROR_STATE();
--put all the errors in a table together
INSERT INTO @ErrorRecap ( AccountName, ErrorMessage )
SELECT@acctname, @ErrorMessage;
--echo out the supressed error, the try catch allows us to continue processing, instead of stopping on the first error
PRINT 'Msg ' + CONVERT(VARCHAR, @ErrorNumber) + ' Level '
+ CONVERT(VARCHAR, @ErrorSeverity) + ' State '
+ CONVERT(VARCHAR, @ErrorState);
PRINT @ErrorMessage;
END CATCH;
FETCH NEXT FROM cuser INTO @acctname;
END;
CLOSE cuser;
DEALLOCATE cuser;
--display both results and errors
SELECT*
FROM#TMP;
SELECT*
FROM#tmpdeeper;
SELECT*
FROM@ErrorRecap;You will see here that I have a couple of cursors built into this script. This is helping me cycle through first the groups and then the users. As I cycle through each user, I am able to determine the validity of the account. Not only will I know if an AD account is dead and needs to be removed from SQL Server, but I will also know if a Domain Group no longer exists.
This little script has proven to be a major time saver with some clients. Not only has it saved me some time, but it has also helped to figure out how a developer was able to perform sysadmin functions on a prod box even though his account did not have sysadmin access granted explicitly.
