Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Active Directory to Display Login Information


Query Active Directory to Display Login Information

Author
Message
Rudy Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1303
Comments posted to this topic are about the item Query Active Directory to Display Login Information



William Soranno
William Soranno
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 514
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
junk.jjk
junk.jjk
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 57
ditto. Please post in multiple lines.
junk.jjk
junk.jjk
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 57
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.';


calvo
calvo
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1378 Visits: 3965
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.

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Rudy Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1303
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 Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1303
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



jmpatchak
jmpatchak
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 86
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 Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1303
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?




jmpatchak
jmpatchak
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 86
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search