Solved

SQL Query to get list of users in Windows Groups

Posted on 2013-06-12
6
454 Views
Last Modified: 2013-07-01
I need a sql query to use in a Data Driven Suscription for SSRS 2008 R2 report that gives me all the users belonging to a certain group. That way any changes in the group will be automatically reflected in the DDS.

Thanks,

monserob
0
Comment
Question by:monserob
  • 3
  • 2
6 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 39241454
I assume all your users and groups are in AD?

If so, you can query AD from SQL Server.  You need to add your AD server as a special linked server, and then you can run OPENQUERY() queries against it.

The process is too long to describe here, but there are several nice write-ups online:

http://www.mssqltips.com/sqlservertip/2580/querying-active-directory-data-from-sql-server/

http://blog.namwarrizvi.com/?p=254

http://www.databasejournal.com/features/mssql/article.php/3849891/Query-Active-Directory-Data-from-SQL-Server-using-T-SQL.htm
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 39241721
To me, easiest is xp_loginfo; for example:

CREATE TABLE #xp_loginfo (
    account_name nvarchar(128),
    type char(8),
    privilege char(10),
    mapped_login_name nvarchar(128),
    permission_path nvarchar(128)
    )
   
INSERT INTO #xp_loginfo
EXEC xp_logininfo 'your_domain_name\your_account_name', 'MEMBERS'

SELECT *
FROM #xp_loginfo
ORDER BY mapped_login_name
0
 

Author Comment

by:monserob
ID: 39245051
Hi nemws1,

Thanks for the quick response, but the groups are not the Active Directory groups. I'm referring to the Windows user groups.

I need to test ScottPhetcher answer, but it seems close to the mark.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 23

Expert Comment

by:nemws1
ID: 39245056
As long as you find a workable solution. :)
0
 

Author Comment

by:monserob
ID: 39289873
Thanks ScottPhetcher,

it's not quite there as I needed to loop trhough the different groups as well, but it pointed me towards the solution.
0
 

Author Comment

by:monserob
ID: 39289881
I've requested that this question be closed as follows:

Accepted answer: 250 points for ScottPletcher's comment #a39241721
Assisted answer: 0 points for monserob's comment #a39289873

for the following reason:

Because the solution suggested by other members didn't quite solve the problem in itself
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question