May 18, 2011 at 11:54 pm
Comments posted to this topic are about the item Query Active Directory to Display Login Information
Rudy
May 19, 2011 at 6:09 am
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.
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'
GO
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
GO
PRINT '... Execute SQL query against Active Directory'
GO
IF OBJECT_ID('tempdb..#ADList') IS NOT NULL
BEGIN
SELECT
'... Dropping temporary table'
DROP TABLE #ADList
END
PRINT '... Collecting data from Active Directory and inserting into temp table'
GO
SELECT
*
INTO
#ADList
FROM
OPENQUERY(ADSI, --Name of the linked server for Active directory
'
SELECT
displayName
,sAMAccountName
,sn
,givenName
,extensionAttribute6
,department
,badPwdCount
,userAccountControl
FROM
''LDAP://DC=MYDOMAIN,DC=COM''
WHERE
objectClass = ''User''
')
GO
PRINT ' ... Displaying data from temp table'
GO
SELECT
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 ASC
GO
PRINT '... Removing temp table'
GO
DROP TABLE #ADList
GO
PRINT '... Removing 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'
GO
PRINT ' AD Data Collection Complete.' ;
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
May 19, 2011 at 6:11 am
ditto. Please post in multiple lines.
May 19, 2011 at 6:37 am
Well, I wanted to try it, so here it is.
/*2011-05-19 Line breaks added by junk.jjk*/
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'
GO
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
GO
PRINT '... Execute SQL query against Active Directory'
GO
IF OBJECT_ID('tempdb..#ADList') IS NOT NULL
BEGIN
SELECT '... Dropping temporary table'
DROP TABLE #ADList
END
PRINT '... Collecting data from Active Directory and inserting into temp table'
GO
SELECT * INTO #ADList
FROM OpenQuery(ADSI, --Name of the linked server for Active directory
'SELECT displayName, sAMAccountName, sn, givenName, extensionAttribute6, department, badPwdCount, userAccountControl
FROM ''LDAP://DC=us,DC=COM''where objectClass = ''User''')
GO
PRINT ' ... Displaying data from temp table'
GO
SELECT 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 ASC
GO
PRINT '... Removing temp table'
GO
DROP TABLE #ADList
GO
PRINT '... Removing 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'
GO
PRINT ' AD Data Collection Complete.';
May 19, 2011 at 6:39 am
May 19, 2011 at 7:12 am
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
William Soranno (5/19/2011)
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.
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'
GO
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
GO
PRINT '... Execute SQL query against Active Directory'
GO
IF OBJECT_ID('tempdb..#ADList') IS NOT NULL
BEGIN
SELECT
'... Dropping temporary table'
DROP TABLE #ADList
END
PRINT '... Collecting data from Active Directory and inserting into temp table'
GO
SELECT
*
INTO
#ADList
FROM
OPENQUERY(ADSI, --Name of the linked server for Active directory
'
SELECT
displayName
,sAMAccountName
,sn
,givenName
,extensionAttribute6
,department
,badPwdCount
,userAccountControl
FROM
''LDAP://DC=MYDOMAIN,DC=COM''
WHERE
objectClass = ''User''
')
GO
PRINT ' ... Displaying data from temp table'
GO
SELECT
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 ASC
GO
PRINT '... Removing temp table'
GO
DROP TABLE #ADList
GO
PRINT '... Removing 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'
GO
PRINT ' AD Data Collection Complete.' ;
Rudy
May 19, 2011 at 7:19 am
calvo (5/19/2011)
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.
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 Disabled
Account Locked Out
Entered Bad Password
No Password is Required
Password CANNOT Change
Normal
Disabled Account
Server Trusted Account for Delegation
Trusted Account for Delegation
Enabled, User Cannot Change Password
Password Never Expires
Account will Never Expire
Enabled and Does NOT expire Paswword
Normal Account, Password will not expire and Currently Disabled
Account Enabled, Password does not expire, currently Locked out
Password has Expired
Hope this help,
Rudy
Rudy
May 19, 2011 at 9:39 am
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?
May 19, 2011 at 10:55 am
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
jmpatchak (5/19/2011)
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?
Rudy
May 19, 2011 at 11:02 am
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.
March 31, 2015 at 8:10 am
Nice!
Works lika a charm. Is there a place where I can find "all" other possible useraccountcontrol values, than those included in the script?
I have also these:
544
546
4096
16777728
16843264
included in this code now:
'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 = 544 THEN 'Enabled, Password Not Required'
WHEN userAccountControl = 546 THEN 'Disabled, Password Not Required'
WHEN userAccountControl = 4096 THEN 'WORKSTATION_TRUST_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')
WHEN userAccountControl = 16777728 THEN 'TRUSTED_TO_AUTH_FOR_DELEGATION'
WHEN userAccountControl = 16843264 THEN 'UF_TRUSTED_TO_AUTHENTICATE_FOR_DELEGATION'
ELSE CONVERT (VARCHAR(248), userAccountControl)
END )
Kind Regards
Jonas
March 31, 2015 at 2:38 pm
Hello,
Great work on your part!! I haven't added any more to the script,yet and don't know of how to add additional information. Really haven't done more with the script.
Thanks,
Rudy
Rudy
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply