I need to query AD to determine if a users account is disabled.
Using a similar query used in the answers here
SELECT *
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName
FROM ''LDAP://DC=MyDC,DC=com,DC=uk''
WHERE objectCategory = ''Person''
AND objectClass = ''user'')
I believe to determine if an account is disabled I have to use the userAccountControl field somehow. I've tried several things but they don't seem to be working:
WHERE userAccountControl & 2 <> 0
The first method to query Active Directory from SQL Server is by using OpenRowSet. If you want to know more about openrowset please read this article. You can access information from Active directory by executing the following query.
Export disabled users from OUGet all disabled users from specific OU in Active Directory and export to CSV file. You need to copy the OU distinguishedName. Paste the OU distinguishedName in the below $OU variable.
Inside OPENQUERY() :
AND ''userAccountControl:1.2.840.113556.1.4.803:''<>2
SELECT *
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName
FROM ''LDAP://DC=MyDC,DC=com,DC=uk''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND ''userAccountControl:1.2.840.113556.1.4.803:''<>2)
How about:
SELECT sAMAccountName
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, userAccountControl
FROM ''LDAP://DC=MyDC,DC=com,DC=uk''
WHERE objectCategory = ''Person''
AND objectClass = ''user''')
WHERE userAccountControl & 2 <> 0; -- disabled
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With