Solved

SQL Query to get list of users in Windows Groups

Posted on 2013-06-12
6
460 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

751 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