Audit Domain Group and User Permissions

, 2017-12-29

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads