List of all users and the groups they belong to

So I have this handy little stored procedure I found that lists all groups that a user is a member of in active directory that can be run from SQL Server Management Studio after creating a linked server to AD.

CREATE PROCEDURE dbo.Get_ADGroups_ForUser
(
    @Username NVARCHAR(256) 
)
AS
BEGIN

    DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024)

    -- Find the fully qualified CN e.g: CN=Beau Holland,OU=Users,OU=Australia,OU=NSO,OU=Company,DC=Domain,DC=local
    -- replace "LDAP://DC=Domain,DC=local" with your own domain
    SET @Query = '
        SELECT @Path = distinguishedName
        FROM OPENQUERY(ADSI, ''
            SELECT distinguishedName 
            FROM ''''LDAP://DC=Domain,DC=local''''
            WHERE 
                objectClass = ''''user'''' AND
                sAMAccountName = ''''' + @Username + '''''
        '')
    '
    EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(1024) OUTPUT', @Path = @Path OUTPUT 

    -- get all groups for a user
    -- replace "LDAP://DC=Domain,DC=local" with your own domain
    SET @Query = '
        SELECT cn,AdsPath
        FROM OPENQUERY (ADSI, ''<LDAP://DC=Domain,DC=local>;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=' + @Path +'));cn, adspath;subtree'')'

    EXEC SP_EXECUTESQL @Query  

END
GO

Open in new window


Question:  What would be the best way such that, if a parameter was NOT supplied, the sproc would return all users and the groups they belong to?  As of now, the sproc requires a user parameter.

It is perfectly acceptable to have this sproc stay as is, requiring the parameter, but have a different sproc that returns all users and the groups they belong to.  Using Cursors is acceptable.  No need to worry about performance.  This AD has only 60 users and about two dozen groups.  We aren't about to run this against a domain controller that belongs to Intel or GE or something.  :)

James
JamesNTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Also look at proc:
sys.xp_logininfo

You could run it first with NULL to capture a list of all groups and users, then cursor through that list to pull, for each one, either 'MEMBER' (for groups) or 'ALL' permissions (for users)
JamesNTAuthor Commented:
Isn't XP_LOGININFO only for groups and users that have been added to SQL Server?  We need this to audit all AD groups.

JamesNT
Scott PletcherSenior DBACommented:
True.  I had expected you needed SQL-related info since you were doing this thru SQL.
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Just define the default value for that parameter as '%'. This will include all users:
CREATE PROCEDURE dbo.Get_ADGroups_ForUser
(
    @Username NVARCHAR(256) ='%'
)
AS
(...)

Open in new window

JamesNTAuthor Commented:
Vitor,

When I do that, all I get is "Commands completed successfully" in the results window.  There is no result set.

James
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you provide an example of the output when you pass a parameter that really works?
JamesNTAuthor Commented:
Yes, of course.
Capture.JPG
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ah, I just realized it now.
When using '%' you need to use the LIKE operator. Just replace the '=' with LIKE:
(...)
    SET @Query = '
        SELECT @Path = distinguishedName
        FROM OPENQUERY(ADSI, ''
            SELECT distinguishedName 
            FROM ''''LDAP://DC=Domain,DC=local''''
            WHERE 
                objectClass = ''''user'''' AND
                sAMAccountName LIKE ''''' + @Username + '''''
(...)

Open in new window

JamesNTAuthor Commented:
Ok, the changes you suggested to the query didn't work.  After reading more about this and how to get information out of AD, I have a question:  Is this the best approach to use?  Or should I be looking at CLR in SQL Server?

James
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why it didn't work? Any error returned?

CLR is a way to use .NET inside SQL Server. It will offer you immediately a vast of possibilities to do that.
Only downside is that you need to configure your SQL Server instance to allow the use of CLR and this might raise some security issues, depending on what the CLR code does.
JamesNTAuthor Commented:
After much debate by our team, we have decided the security risks are acceptable and can be mitigated.  Therefore, we are going the CLR route for the greater flexibility.

James

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
JamesNT, if your issue has been solved please close this question by accepting the comment or comments that helped you out.
If not, then let us know how can we help you further.
Cheers
JamesNTAuthor Commented:
Decided to take a different path to solve the problem the question was asking about.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.