Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Active Directory to Display Login Information Expand / Collapse
Author
Message
Posted Wednesday, May 18, 2011 11:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 315, Visits: 1,113
Comments posted to this topic are about the item Query Active Directory to Display Login Information


Post #1111482
Posted Thursday, May 19, 2011 6:09 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:46 AM
Points: 77, Visits: 436
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
Post #1111594
Posted Thursday, May 19, 2011 6:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 13, 2011 2:23 PM
Points: 21, Visits: 56
ditto. Please post in multiple lines.
Post #1111595
Posted Thursday, May 19, 2011 6:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 13, 2011 2:23 PM
Points: 21, Visits: 56
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.';

Post #1111625
Posted Thursday, May 19, 2011 6:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 1,259, Visits: 3,569
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.
Post #1111628
Posted Thursday, May 19, 2011 7:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 315, Visits: 1,113
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.' ;




Post #1111664
Posted Thursday, May 19, 2011 7:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 315, Visits: 1,113
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



Post #1111668
Posted Thursday, May 19, 2011 9:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:19 AM
Points: 52, Visits: 75
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?
Post #1111822
Posted Thursday, May 19, 2011 10:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 315, Visits: 1,113
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?



Post #1111877
Posted Thursday, May 19, 2011 11:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:19 AM
Points: 52, Visits: 75
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.
Post #1111884
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse