SQLServerCentral / Discuss content posted by Rudy Panigas / Article Discussions / Article Discussions by Author / Query Active Directory to Display Login Information / Latest Posts

RE: Query Active Directory to Display Login Information

jmpatchak — Thu, 19 May 2011 11:02:25 GMT

Thanks Rudy - I've used the AD API before and I know that's limited to 1000. I just thought it was odd that the number here through open query appears to be 901. I will have to pull just certain OU's at a time, but this is extremely useful for me. Thank you.

RE: Query Active Directory to Display Login Information

Rudy Panigas — Thu, 19 May 2011 10:55:17 GMT

I believe the limit is set to 1000. Not sure of a work around so you may have to discuss with your Network/Windows administrators. You may have to divide your queries based on departments or groups in order to reduce the about of data returned.Thanks,Rudy[quote][b]jmpatchak (5/19/2011)[/b][hr]Looks to me like I get an error unless I bind the results to just the first 901. Is that consistent with what others are seeing? If so, is there a way to get around that?[/quote]

RE: Query Active Directory to Display Login Information

jmpatchak — Thu, 19 May 2011 09:39:28 GMT

Looks to me like I get an error unless I bind the results to just the first 901. Is that consistent with what others are seeing? If so, is there a way to get around that?

RE: Query Active Directory to Display Login Information

Rudy Panigas — Thu, 19 May 2011 07:19:19 GMT

[quote][b]calvo (5/19/2011)[/b][hr]Perhaps a little explanation as to what the script does or how it does it. Might be helpful for those that are not familiar with these operations.[/quote]My apologies to everyone. It seems that my post was having issues and the description is not showing up. The script will show the information of the accounts that are in active directory (Windows Authentication). You can modify the code to just show users that you are interested.Below is the information about the AD logins produced:Account is DisabledAccount Locked OutEntered Bad PasswordNo Password is RequiredPassword CANNOT ChangeNormalDisabled AccountServer Trusted Account for DelegationTrusted Account for DelegationEnabled, User Cannot Change PasswordPassword Never ExpiresAccount will Never ExpireEnabled and Does NOT expire PaswwordNormal Account, Password will not expire and Currently DisabledAccount Enabled, Password does not expire, currently Locked outPassword has ExpiredHope this help,Rudy

RE: Query Active Directory to Display Login Information

Rudy Panigas — Thu, 19 May 2011 07:12:19 GMT

Thank for the updated version. It's strange that the script was messed up. When I submitted the code it looked good but now I see that it's showing up as one line.Must be from all the rain we are getting.Thanks,Rudy[quote][b]William Soranno (5/19/2011)[/b][hr]Rudy, You should edit you post so the code is multiple lines. One long line is diffucult to read.Here is a multi line version.[code="sql"]PRINT 'Active Directory Query Script'PRINT ''PRINT '... Creating link conenction to Active Directory' GO IF EXISTS ( SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ADSI' ) EXEC master.dbo.sp_dropserver @server = N'ADSI', @droplogins = 'droplogins'GOsp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource' GO PRINT '... Execute SQL query against Active Directory'GOIF OBJECT_ID('tempdb..#ADList') IS NOT NULL BEGIN SELECT '... Dropping temporary table' DROP TABLE #ADList ENDPRINT '... Collecting data from Active Directory and inserting into temp table'GOSELECT *INTO #ADListFROM OPENQUERY(ADSI, --Name of the linked server for Active directory 'SELECT displayName ,sAMAccountName ,sn ,givenName ,extensionAttribute6 ,department ,badPwdCount ,userAccountControlFROM ''LDAP://DC=MYDOMAIN,DC=COM''WHERE objectClass = ''User'' ')GOPRINT ' ... Displaying data from temp table'GOSELECT displayName AS 'Display Name' ,sn AS 'Surname' ,givenName AS 'Given Name' ,sAMAccountName AS 'Account Name' ,extensionAttribute6 AS 'Computer Name' ,'bad-Pwd' = ( CASE WHEN badPwdCount = 17 THEN 'Entered Bad Password' ELSE badPwdCount END ) ,'AcctCtrl' = ( CASE WHEN userAccountControl = 2 THEN 'Account is Disabled' WHEN userAccountControl = 16 THEN 'Account Locked Out' WHEN userAccountControl = 17 THEN CONVERT (VARCHAR(48), 'Entered Bad Password') WHEN userAccountControl = 32 THEN CONVERT (VARCHAR(48), 'No Password is Required') WHEN userAccountControl = 64 THEN CONVERT (VARCHAR(48), 'Password CANNOT Change') WHEN userAccountControl = 512 THEN 'Normal' WHEN userAccountControl = 514 THEN 'Disabled Account' WHEN userAccountControl = 8192 THEN 'Server Trusted Account for Delegation' WHEN userAccountControl = 524288 THEN 'Trusted Account for Delegation' WHEN userAccountControl = 590336 THEN 'Enabled, User Cannot Change Password, Password Never Expires' WHEN userAccountControl = 65536 THEN CONVERT (VARCHAR(48), 'Account will Never Expire') WHEN userAccountControl = 66048 THEN 'Enabled and Does NOT expire Paswword' WHEN userAccountControl = 66050 THEN 'Normal Account, Password will not expire and Currently Disabled' WHEN userAccountControl = 66064 THEN 'Account Enabled, Password does not expire, currently Locked out' WHEN userAccountControl = 8388608 THEN CONVERT (VARCHAR(48), 'Password has Expired') ELSE CONVERT (VARCHAR(248), userAccountControl) END )FROM #ADListWHERE givenName IS NOT NULLORDER BY sn ASCGOPRINT '... Removing temp table'GODROP TABLE #ADListGOPRINT '... Removing link conenction to Active Directory'GOIF EXISTS ( SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ADSI' ) EXEC master.dbo.sp_dropserver @server = N'ADSI', @droplogins = 'droplogins'GOPRINT ' AD Data Collection Complete.' ; [/code][/quote]

RE: Query Active Directory to Display Login Information

calvo — Thu, 19 May 2011 06:39:12 GMT

Perhaps a little explanation as to what the script does or how it does it. Might be helpful for those that are not familiar with these operations.

RE: Query Active Directory to Display Login Information

junk.jjk — Thu, 19 May 2011 06:37:14 GMT

Well, I wanted to try it, so here it is.[code]/*2011-05-19 Line breaks added by junk.jjk*/PRINT 'Active Directory Query Script'PRINT ''PRINT '... Creating link conenction to Active Directory'GOIF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ADSI')EXEC master.dbo.sp_dropserver @server=N'ADSI', @droplogins='droplogins'GOsp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'GO PRINT '... Execute SQL query against Active Directory'GOIF OBJECT_ID('tempdb..#ADList') IS NOT NULLBEGIN SELECT '... Dropping temporary table' DROP TABLE #ADListENDPRINT '... Collecting data from Active Directory and inserting into temp table'GOSELECT * INTO #ADList FROM OpenQuery(ADSI, --Name of the linked server for Active directory'SELECT displayName, sAMAccountName, sn, givenName, extensionAttribute6, department, badPwdCount, userAccountControlFROM ''LDAP://DC=us,DC=COM''where objectClass = ''User''')GOPRINT ' ... Displaying data from temp table'GOSELECT displayName AS 'Display Name',sn AS 'Surname',givenName AS 'Given Name', sAMAccountName AS 'Account Name', extensionAttribute6 AS 'Computer Name', 'bad-Pwd' = (CASE WHEN badPwdCount = 17 THEN 'Entered Bad Password' ELSE badPwdCount END), 'AcctCtrl' = (CASE WHEN userAccountControl = 2 THEN 'Account is Disabled' WHEN userAccountControl = 16 THEN 'Account Locked Out' WHEN userAccountControl = 17 THEN CONVERT (VARCHAR(48),'Entered Bad Password') WHEN userAccountControl = 32 THEN CONVERT (VARCHAR(48),'No Password is Required') WHEN userAccountControl = 64 THEN CONVERT (VARCHAR(48),'Password CANNOT Change') WHEN userAccountControl = 512 THEN 'Normal' WHEN userAccountControl = 514 THEN 'Disabled Account' WHEN userAccountControl = 8192 THEN 'Server Trusted Account for Delegation' WHEN userAccountControl = 524288 THEN 'Trusted Account for Delegation' WHEN userAccountControl = 590336 THEN 'Enabled, User Cannot Change Password, Password Never Expires' WHEN userAccountControl = 65536 THEN CONVERT (VARCHAR(48),'Account will Never Expire') WHEN userAccountControl = 66048 THEN 'Enabled and Does NOT expire Paswword' WHEN userAccountControl = 66050 THEN 'Normal Account, Password will not expire and Currently Disabled' WHEN userAccountControl = 66064 THEN 'Account Enabled, Password does not expire, currently Locked out' WHEN userAccountControl = 8388608 THEN CONVERT (VARCHAR(48),'Password has Expired') ELSE CONVERT (VARCHAR(248),userAccountControl) END) FROM #ADList WHERE givenName IS NOT NULL ORDER BY sn ASCGOPRINT '... Removing temp table'GODROP TABLE #ADListGOPRINT '... Removing link conenction to Active Directory'GOIF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ADSI')EXEC master.dbo.sp_dropserver @server=N'ADSI', @droplogins='droplogins'GOPRINT ' AD Data Collection Complete.'; [/code]

RE: Query Active Directory to Display Login Information

junk.jjk — Thu, 19 May 2011 06:11:34 GMT

ditto. Please post in multiple lines.

RE: Query Active Directory to Display Login Information

William Soranno — Thu, 19 May 2011 06:09:01 GMT

Rudy, You should edit you post so the code is multiple lines. One long line is diffucult to read.Here is a multi line version.[code="sql"]PRINT 'Active Directory Query Script'PRINT ''PRINT '... Creating link conenction to Active Directory' GO IF EXISTS ( SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ADSI' ) EXEC master.dbo.sp_dropserver @server = N'ADSI', @droplogins = 'droplogins'GOsp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource' GO PRINT '... Execute SQL query against Active Directory'GOIF OBJECT_ID('tempdb..#ADList') IS NOT NULL BEGIN SELECT '... Dropping temporary table' DROP TABLE #ADList ENDPRINT '... Collecting data from Active Directory and inserting into temp table'GOSELECT *INTO #ADListFROM OPENQUERY(ADSI, --Name of the linked server for Active directory 'SELECT displayName ,sAMAccountName ,sn ,givenName ,extensionAttribute6 ,department ,badPwdCount ,userAccountControlFROM ''LDAP://DC=MYDOMAIN,DC=COM''WHERE objectClass = ''User'' ')GOPRINT ' ... Displaying data from temp table'GOSELECT displayName AS 'Display Name' ,sn AS 'Surname' ,givenName AS 'Given Name' ,sAMAccountName AS 'Account Name' ,extensionAttribute6 AS 'Computer Name' ,'bad-Pwd' = ( CASE WHEN badPwdCount = 17 THEN 'Entered Bad Password' ELSE badPwdCount END ) ,'AcctCtrl' = ( CASE WHEN userAccountControl = 2 THEN 'Account is Disabled' WHEN userAccountControl = 16 THEN 'Account Locked Out' WHEN userAccountControl = 17 THEN CONVERT (VARCHAR(48), 'Entered Bad Password') WHEN userAccountControl = 32 THEN CONVERT (VARCHAR(48), 'No Password is Required') WHEN userAccountControl = 64 THEN CONVERT (VARCHAR(48), 'Password CANNOT Change') WHEN userAccountControl = 512 THEN 'Normal' WHEN userAccountControl = 514 THEN 'Disabled Account' WHEN userAccountControl = 8192 THEN 'Server Trusted Account for Delegation' WHEN userAccountControl = 524288 THEN 'Trusted Account for Delegation' WHEN userAccountControl = 590336 THEN 'Enabled, User Cannot Change Password, Password Never Expires' WHEN userAccountControl = 65536 THEN CONVERT (VARCHAR(48), 'Account will Never Expire') WHEN userAccountControl = 66048 THEN 'Enabled and Does NOT expire Paswword' WHEN userAccountControl = 66050 THEN 'Normal Account, Password will not expire and Currently Disabled' WHEN userAccountControl = 66064 THEN 'Account Enabled, Password does not expire, currently Locked out' WHEN userAccountControl = 8388608 THEN CONVERT (VARCHAR(48), 'Password has Expired') ELSE CONVERT (VARCHAR(248), userAccountControl) END )FROM #ADListWHERE givenName IS NOT NULLORDER BY sn ASCGOPRINT '... Removing temp table'GODROP TABLE #ADListGOPRINT '... Removing link conenction to Active Directory'GOIF EXISTS ( SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ADSI' ) EXEC master.dbo.sp_dropserver @server = N'ADSI', @droplogins = 'droplogins'GOPRINT ' AD Data Collection Complete.' ; [/code]

Query Active Directory to Display Login Information

Rudy Panigas — Wed, 18 May 2011 23:54:46 GMT

Comments posted to this topic are about the item [B]Query Active Directory to Display Login Information[/B]