Query Active Directory to Display Login Information

  • Rudy Panigas

    SSChampion

    Points: 10695

    Comments posted to this topic are about the item Query Active Directory to Display Login Information

    Rudy

  • William Soranno

    SSCommitted

    Points: 1608

    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

    Mr or Mrs. 500

    Points: 545

    ditto. Please post in multiple lines.

  • junk.jjk

    Mr or Mrs. 500

    Points: 545

    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

    SSChampion

    Points: 12930

    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.[/url] Get your answers faster.

  • Rudy Panigas

    SSChampion

    Points: 10695

    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

  • Rudy Panigas

    SSChampion

    Points: 10695

    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

  • jmpatchak

    SSC-Addicted

    Points: 467

    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

    SSChampion

    Points: 10695

    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

  • jmpatchak

    SSC-Addicted

    Points: 467

    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.

  • Jonas Bergström

    SSC-Addicted

    Points: 453

    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

  • Rudy Panigas

    SSChampion

    Points: 10695

    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 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply