CODEWITHSUNDEEP
sql
perplexedDev
perplexedDev

Reputation: 835

Get the list of Users from Security group in active directory using SQL

I have a table which has the list of members of a security group. I need to do a check everyday to check if new members have been added or deleted and update the table accordingly. Is there a way to get the list of members of a security group in Active Directory using SQL?

Upvotes: 1

Views: 1569

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32155

Linked server to Active Directory is the usual method.

Here is a somewhat better, step-by-step approach to setting it up.

Then create a view that has your LDAP query, and you should be good to go.

Here's what I use for one of my systems:

CREATE VIEW adsi_to_app AS
SELECT convert(VARCHAR(20), u.sAMAccountName) collate SQL_Latin1_General_CP1_CI_AS "username",
    CASE 
        WHEN (u.userAccountControl & 2) <> 0
            THEN 'Disabled'
        WHEN CASE convert(BIGINT, u.accountExpires)
                WHEN 0
                    THEN '12/31/9999'
                WHEN 9223372036854775807
                    THEN '12/31/9999'
                ELSE dateadd(mi, (convert(BIGINT, u.accountExpires) / 600000000) - 157258080 + datediff(minute, GetUTCDate(), GetDate()), 0)
                END < getdate()
            THEN 'Expired'
        ELSE 'Active'
        END "account_status"
FROM OPENQUERY(ADSI, '<LDAP://DC=your,DC=domain,DC=goes,DC=here>;(&(objectCategory=Person)(objectClass=User)(memberOf=CN=GroupName,OU=Groups,DC=your,DC=domain,DC=goes,DC=here));sAMAccountName,userAccountControl,accountExpires;subtree') u

The collation is because the server has case-sensitive collation. The ADSI in the OPENQUERY() is the name of the linked server object.

Note that you may need to enable ad hoc queries to use OPENQUERY().

Upvotes: 1

Related Questions