October 11, 2017 at 6:07 am
Given that I run a powershell script to capture all my AD Users and their Groups - is the a way of listing what report each person has access to in SSRS.
We have an audit due and management want a comprehensive list.
This is doing my head in.
Thanks in Advance
October 11, 2017 at 8:36 am
epowell 61898 - Wednesday, October 11, 2017 6:07 AMGiven that I run a powershell script to capture all my AD Users and their Groups - is the a way of listing what report each person has access to in SSRS.
We have an audit due and management want a comprehensive list.
This is doing my head in.Thanks in Advance
I don't know of a way to get them directly with a built in Poweshell cmdlet but you can use a query in the ReportServer database.
Most of the SSRS permissions checks use the same joins with the PolicyUserRole table in the ReportServer database that has foreign keys to Policies, Users, and Roles. So the joins are generally the same with the rest of the query depending on what you need. For just users and reports, try something along the lines of:
SELECT DISTINCT U.UserName,
C.Path,
C.Name as Report
FROM dbo.PolicyUserRole PR
INNER JOIN dbo.Policies P
ON PR.PolicyID = P.PolicyID
INNER JOIN dbo.Users U
ON PR.UserID = U.UserID
INNER JOIN dbo.Roles R
ON PR.RoleID = R.RoleID
INNER JOIN dbo.Catalog C
ON PR.PolicyID = C.PolicyID
WHERE C.Type = 2
ORDER BY U.UserName;
Sue
October 11, 2017 at 9:01 am
Sue_H - Wednesday, October 11, 2017 8:36 AMepowell 61898 - Wednesday, October 11, 2017 6:07 AMGiven that I run a powershell script to capture all my AD Users and their Groups - is the a way of listing what report each person has access to in SSRS.
We have an audit due and management want a comprehensive list.
This is doing my head in.Thanks in Advance
I don't know of a way to get them directly with a built in Poweshell cmdlet but you can use a query in the ReportServer database.
Most of the SSRS permissions checks use the same joins with the PolicyUserRole table in the ReportServer database that has foreign keys to Policies, Users, and Roles. So the joins are generally the same with the rest of the query depending on what you need. For just users and reports, try something along the lines of:
SELECT DISTINCT U.UserName,
C.Path,
C.Name as Report
FROM dbo.PolicyUserRole PR
INNER JOIN dbo.Policies P
ON PR.PolicyID = P.PolicyID
INNER JOIN dbo.Users U
ON PR.UserID = U.UserID
INNER JOIN dbo.Roles R
ON PR.RoleID = R.RoleID
INNER JOIN dbo.Catalog C
ON PR.PolicyID = C.PolicyID
WHERE C.Type = 2
ORDER BY U.UserName;Sue
And here was me experimenting with Powershell cmdlet's for the last 30 minutes... /facepalm.
Thanks Sue, this'll be helpful for the future. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 11, 2017 at 4:47 pm
Sorry Sue been there - done that
Your SQL ONLY works for the circumstance where you directly assign a user to a folder or report - if that you are user that is part of a group - you cannot determine if they have access.
You don't seem to be able to say "Billy Bloggs" is a member of the Active Directory Accounts Group which has access to the Accounts Folder which contains these reports, even though the SSRS security screens reflect that.
See the dilemma .
October 11, 2017 at 5:04 pm
epowell 61898 - Wednesday, October 11, 2017 4:47 PMSorry Sue been there - done that
Your SQL ONLY works for the circumstance where you directly assign a user to a folder or report - if that you are user that is part of a group - you cannot determine if they have access.
You don't seem to be able to say "Billy Bloggs" is a member of the Active Directory Accounts Group which has access to the Accounts Folder which contains these reports, even though the SSRS security screens reflect that.
See the dilemma .
Just because you aren't given the information in the manner you would like doesn't mean it can't be done. It's certainly doable. If you get the group and you already have the group and the members then you do know which users have access. You'll need to do some work but it is doable. The information is all there. Dealing with AD groups in SQL Server and audits isn't uncommon.
October 11, 2017 at 6:01 pm
What a strange reply to a technical question.
Sorry - I am seeking a solution Sue was trying to help - I was eluding to her response which I am very grateful for, but it does resolve question - and I didn't want people to believe that it is a solution.
October 11, 2017 at 6:39 pm
epowell 61898 - Wednesday, October 11, 2017 6:01 PMWhat a strange reply to a technical question.
Sorry - I am seeking a solution Sue was trying to help - I was eluding to her response which I am very grateful for, but it does resolve question - and I didn't want people to believe that it is a solution.
You run a script in Powershell to get the AD groups and the users.
Results from Powershell can be inserted into a table or table(s).
You can do select into or insert into to have the results of another query in a table.
And then you write a query. And that is just one way and there are likely others.
You have the groups, you have the members of the groups, you have the permissions for either the groups and/or users. You think that isn't something that can be done with a query? What specifically is missing?
Look at your Powershell script, figure out the tables to create, modify the powershell script so the results are put into a table or tables, create a table for the permissions from SSRS and write a query.
Sue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply