Solved

SQL Query to get list of users in Windows Groups

Posted on 2013-06-12
6
472 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

622 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