Skip to main content

Querying Active Directory from SQL Server

At many companies, access to employees PC resources (USB drive, CD ROM drive, root of C: etc) is managed by Group Policy. Sometimes Group Policy with the requisite settings is applied to a Security Group so members of that group inherit the settings of that GPO. So you may end up with many different policies applied to many different groups.

This is a straight forward easy way to manage GPOs - if you want to know what access a user has to their PC simply look at their group membership.

Being a database type person it occurred to me, it would be nice if we could query Active Directory at regular intervals to see who has what permissions to their PC (for security auditing), populate a table with the results and perhaps point Excel at the table to create a nice report for management. (I won't cover the Excel part in this post but there is very little to it)

I looked (Googled) various different ways this could be achieved but nothing quite did it the way I wanted so in the end had to come up with my own solution.

A popular solution can be found at mssqltips.com using a linked server to a Domain Controller and ADSI but the problem I found with it is that I couldn't find an easy way to drill down through AD to find the group objects - If all the groups you are interested in are in the same OU then it worked fine but if they were spread about in different OUs, some at the same level of the tree and others off to one side or the other then you had to write separate code to address each OU. Which is fine until someone moves a group to a different OU due to tidy up and then your query breaks.

Other solutions suggested using WMI but I had no luck there either. So in the end I went for the command line tools DSQuery and DSGet called by xp_CmdShell. With these tools you don't have to use an LDAP path, you just run them as you would any other command line tool from with SQL Server. Both tools are available in the Remove Server Administration Tools.

There are several issues with the Remove Server Administration Tools approach:

  1. Some shops don't like xp_CmdShell to be enabled due to a perceived security issue (not a biggy IMHO)
  2. The account you run the tools under needs permissions to Active Directory
  3. DSGet and DSQuery have to be installed on the SQL Server you will be querying from. See here for instructions on how to do it.
  4. The output from DSGet and DSQuery is nasty and requires a lot of cleaning up before it fits nicely into fields in a table. A big part of the query below is making the results returned usable.
Your business may have a policy for the first three points which may restrict your use of this script, but assuming you are good to go feel free to use it (please read the comments in line for further info and as usual I take NO responsibility for any issues AT ALL if you execute the script on ANY server - but it should be fine!). The guts of the script are taken from this command:

dsquery group -name GG-C_Drive_Access | dsget group -members -expand -c | dsget user -samid -display -dept -c | Find /V "dsget succeeded" | Find /V "samid" | Find /V "_"

What this is saying is query the group GG-C_Drive_Access and pipe the results to dsget group and get the member attributes samid, display and dept. This is piped to Find to not return the lines that contain dsget succeeded, samid and the underscore (you may have different/additional requirements).

The query depends on a function called ufn_Split

/******************************************************************************************
Name    : Show Groups, members and departments from Active Directory.sql
Author  : Paul Hewson (IT Operations DBA)
Purpose : This script uses dsquery and dsget (from the Remote Administration
                Tools) to query the membership of the specified Global Security Groups. These groups determine the rights a user has to their PC. 

-------------------------
Version    :     1.0
Date       : 02 May 2014
Info       : Initial version
Depends on : ufn_split
*******************************************************************************************/



USE DBA;
GO
/*Drop the tables that will be used if they exist*/
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#Groups')) DROP TABLE #Groups
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#Temp')) DROP TABLE #Temp
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#GroupsToCheck')) DROP TABLE #GroupsToCheck
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#GroupMembershipTemp')) DROP TABLE #GroupMembershipTemp
IF EXISTS (SELECT 1 FROM DBA..sysobjects WHERE id=OBJECT_ID('DBA..GroupMembership')) DROP TABLE GroupMembership

/*Create the tables */
CREATE TABLE #Groups (GroupName VARCHAR(100), Member VARCHAR (400))
CREATE TABLE #Temp (Member VARCHAR (400))
CREATE TABLE #GroupMemberShipTemp(ID INT IDENTITY (1,1),GroupName VARCHAR(50), Field VARCHAR(100), Member VARCHAR(100), Polldate DATETIME) 
CREATE TABLE GroupMemberShip(ID INT, GroupName VARCHAR(50), Username VARCHAR(100), Fullname VARCHAR(100), Dept VARCHAR(100), Polldate DATETIME)

/*The list of Security Groups to check. You may add/delete from this list as required*/
CREATE TABLE #GroupsToCheck(ID INT IDENTITY (1,1), GroupName VARCHAR(50))
INSERT #GroupsToCheck
SELECT 'GG-C_Drive_Access' UNION ALL
SELECT 'GG-D_Drive_Access' UNION ALL
SELECT 'GG-Allow_Command_Prompt_Access' UNION ALL
SELECT 'GG-Local_Admin'

/*Cursor to populate the #groups table with the unformatted  output of dsquery*/
DECLARE @GroupName VARCHAR(100)
DECLARE @Member VARCHAR(400)
DECLARE @SQL VARCHAR(MAX)

DECLARE curADGroups CURSOR FOR
SELECT
       GroupName
FROM
       #GroupsToCheck
      
OPEN curADGroups

FETCH NEXT FROM curADGroups INTO @Groupname

WHILE @@FETCH_STATUS = 0
       BEGIN
                TRUNCATE TABLE #temp
               
                SET @SQL = 'EXEC MASTER..XP_CMDSHELL ''dsquery group -name "'
                SELECT @SQL = @SQL + @groupName
                SET @SQL = @SQL + '" | dsget group -members -expand -c | dsget user -samid -display -dept -c | Find /V "dsget succeeded" | Find /V "samid" | Find /V "_"'''
                --PRINT @SQL
                INSERT #temp
                EXEC (@SQL)

                INSERT #groups
                SELECT @GroupName, Member
                From #temp

              FETCH NEXT FROM curADGroups INTO @GroupName
       END

CLOSE curADGroups
DEALLOCATE curADGroups

/*The unformatted output from dsquery includes the Username, Fullname and Department of an AD user
in one column separated by two spaces. This query splits these values into separate rows with an ordinal number
that is renamed appropriately.*/  
INSERT #GroupMemberShipTemp
SELECT 
          GroupName
          ,CASE s.ordinal WHEN 1 Then 'UserName' WHEN    2 Then 'FullName' WHEN 3 THEN 'Dept' END AS Field
          ,s.StringValue
          ,GETDATE()
FROM #groups u
          CROSS APPLY master.dbo.ufn_split(REPLACE(REPLACE(REPLACE(RTRIM(lTRIM(u.member)),'  ','<>'),'><',''),'<>',','), ',') s

/*Pivot the output from the previous query into the correct columns*/
;WITH groupCTE AS
(
SELECT
    ID
    ,GroupName
    ,(SELECT member FROM #GroupMemberShipTemp g2 WHERE field = 'UserName' AND g2.ID = g.ID ) AS UserName
    ,(SELECT member FROM #GroupMemberShipTemp g3 WHERE field = 'FullName' AND g3.ID = g.ID + 1 ) AS FullName
    ,(SELECT member FROM #GroupMemberShipTemp g4 WHERE field = 'Dept' AND g4.ID = g.ID + 2 ) AS Dept
    ,Polldate
   
 FROM #GroupMemberShipTemp g
 )
INSERT GroupMembership
SELECT
    ID
       ,GroupName
       ,LTRIM(RTRIM(UserName)) AS UserName
       ,LTRIM(RTRIM(FullName)) AS FullName
       ,LTRIM(RTRIM(Dept)) AS Dept
       ,Polldate
FROM groupCTE
WHERE UserName IS NOT NULL
ORDER BY GroupName
       ,UserName


<end>





 

Comments

Popular posts from this blog

How to move the Microsoft Assessment and Planning Toolkit (MAP) database to a different drive

The Microsoft Assessment and Planning Toolkit (MAP) is a very useful tool for scanning your network to find instances of SQL Server plus all manner of detailed information about the installed product, OS and hardware it sits on.


<Click image to enbiggen>
There is an issue with it the database it uses to store the data it collects, however. Assuming you don't have an instance called MAPS on your server, the product will install using LocalDB (a cut down version of SQL Server Express) and puts the databases on your C: drive. If you then scan a large network you could easily expand the database to 10GB which may cause issues on a server when that drive is often one of the smallest. However, there is a simple solution: connect to LocalDB using Management Studio, detach the databases, move to a different drive, set permissions on the new location if required and reattach the database. How do you connect to LocalDB? Here you go:

Connect to (localdb)\MAPTOOLKIT


The databases I move…

Generate scripts to attach multiple databases

There is a handy little "by product", if you like, when running queries which means you can quickly generate scripts to do different things. Below is an example of generating multiple "attach" commands that you can copy from the results pane into the main SSMS window for execution. I have found this very handy in the past:


SELECT 'CREATE DATABASE ['+name+'] ON ( FILENAME = N''F:\MSSQL\Data\'+name+'.mdf'' ), ( FILENAME = N''E:\MSSQL\Log\'+name+'_log.ldf'' )  FOR ATTACH GO ' FROMmaster.dbo.sysdatabases WHEREnamenotin('master','msdb','model','tempdb')
ORDERBYname

The Purge SQL Agent Job for MDW takes a long time to complete

I use the dbWarden alerts to inform me if a SQL job is taking longer to complete than normal and I got one this morning:
I noticed by looking at the history this purge job was gradually taking longer and longer to complete each day since I installed it again (see mylast post on this):
RunDate RunTime Duration ExecutionStatus JobName 04/10/2013 02:00:01 00:00:45 Succeded mdw_purge_data_[MDW] 05/10/2013 02:00:00 00:13:27 Succeded mdw_purge_data_[MDW] 06/10/2013 02:00:00 00:17:03 Succeded mdw_purge_data_[MDW] 07/10/2013 02:00:01