Query AD (Active Directory)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Query AD (Active Directory)

Query AD (Active Directory)

Rate Topic

Display Mode

Topic Options

Author

Message

kd11

Posted Wednesday, October 03, 2012 11:39 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 1:58 PM
Points: 94, Visits: 865

I know you can query AD for logins,email,location but if I want to know if the users are active or not, can I pull that as well. I'm not seeing it in the container if so.

Post #1367893

Lowell

Posted Wednesday, October 03, 2012 11:55 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 11,605, Visits: 27,649

this site has the most comprehensive list of attributes you can query that i've ever tripped over:
http://www.rlmueller.net/UserAttributes.htm

specifically, his excel spreadsheet has a ton of stuff:
http://www.rlmueller.net/References/Schema.xls

i searched for "locked" and "enabled" and "disabled", and didn't see anything that seemed to me to be specific to a user
you may have a better idea on what to search for than me.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1367914

kd11

Posted Thursday, October 04, 2012 6:29 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 1:58 PM
Points: 94, Visits: 865

Thanks, that was very helpful.

Post #1368345

Scott D. Jacobson

Posted Thursday, October 04, 2012 8:01 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:17 PM
Points: 335, Visits: 841

Any chance you can share what the attribute was called? As Lowell mentioned he didn't find anything obvious. I did a quick skim through but the list is quite long.

Post #1368427

Randy Doub

Posted Thursday, October 04, 2012 1:59 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:28 PM
Points: 131, Visits: 597

I exclude Disabled in a vb script that queries AD.

Here’s the check for a user being disabled in AD:
(userAccountControl:1.2.840.113556.1.4.803:=2)

So to exclude use (!userAccountControl:1.2.840.113556.1.4.803:=2) in the filter:

strFilter = "(&(objectCategory=person)(!userAccountControl:1.2.840.113556.1.4.803:=2)(objectClass=user)(memberOf=cn=CERUsers,ou=CER,ou=intranet applications,ou=groups,ou=khs,dc=myhouse,dc=org))"

From the VB script:

' Open the output file for write access.
Set objFile = objFSO.OpenTextFile(strFilePath, 2, True, 0)
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
Set objCommand = CreateObject("ADODB.Command")
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider","myhouse\mylogin","mypassword"
objCommand.ActiveConnection = objConnection
strBase = "<LDAP://" & strDNSDomain & ">"
strFilter = "(&(objectCategory=person)(!userAccountControl:1.2.840.113556.1.4.803:=2)(objectClass=user)(memberOf=cn=Distribution Group Corporate Management,ou=Mail Groups,ou=groups,ou=khs,dc=myhouse,dc=org))" 
strAttributes = "cn,displayName,mail,title,physicalDeliveryOfficeName"
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
Set objRecordSet = objCommand.Execute

Post #1368686

Scott D. Jacobson

Posted Monday, October 08, 2012 8:14 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:17 PM
Points: 335, Visits: 841

Thank you for that Randy. It's always good to have the solution for others to find in the future.

Post #1369841

« Prev Topic | Next Topic »

Permissions