Solved

SQL Query to get list of users in Windows Groups

Posted on 2013-06-12
6
448 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
Comment Utility
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:
ScottPletcher earned 250 total points
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 23

Expert Comment

by:nemws1
Comment Utility
As long as you find a workable solution. :)
0
 

Author Comment

by:monserob
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Date Range Syntax Access 2003 10 44
SQL Authentication and Win NT Authentication Issues 20 37
query question 13 40
Sql query for filter 12 18
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now