Check if AD Group has Changed with XP_Login
After a few drinks with some friends last week we got talking about how it would be nice to monitor some Active Directory groups and find out if any changes have been made, I surmised that it would be pretty easy with PowerShell using either the Quest tools or the new Posh 3 AD cmdlets and compare-object to know when changes had been made. Before I started playing with PowerShell ISE I did my normal Google search and most impressively Francois-Xavier Cat (The LazyWinAdmin – http://www.lazywinadmin.com/) has wrote just that and with fancy reports and history far greater than I would ever do. The post is here :
So still with my drunken hat on, I thought why do these things in PowerShell when we have the always versatile SQL Server. As I’ve posted before XP_LOGININFO (https://sqlnotesfromtheunderground.wordpress.com/2013/12/10/troubleshoot-user-logins/) can return information about AD Group members, and using the EXCEPT command, we can compare results.
Anyway, its not finished and would need adding some code to send an email with the results, but if anyone is interested in doing this sort of thing within SQL it certainly seems possible
CREATE TABLE #Usergroup ( names VARCHAR(100) ) INSERT INTO #Usergroup ( names ) VALUES ( 'BADSEEDS\Stephen' ) INSERT INTO #Usergroup ( names ) VALUES ( 'BADSEEDS\Timmy' ) CREATE TABLE #currentGroup ( accountName VARCHAR(100) , type VARCHAR(100) , priviege VARCHAR(100) , mappedLoginName VARCHAR(100) , permission VARCHAR(100) ) INSERT INTO #currentGroup EXEC xp_logininfo 'badseeds\SysAdminsGroup', 'members' DECLARE @changes INT SELECT @changes = COUNT(*) FROM ( SELECT accountName FROM #currentGroup EXCEPT SELECT names FROM #Usergroup ) a IF ( @changes > 0 ) PRINT 'we got issues email statement here! ' ELSE PRINT 'we are all good' DROP TABLE #currentGroup DROP TABLE #Usergroup