Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query to get list of users in Windows Groups

Posted on 2013-06-12
6
Medium Priority
?
488 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 70

Accepted Solution

by:
Scott Pletcher earned 750 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
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!

 
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
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.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

824 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