SQLTeam.com | Weblogs | Forums

Check specific user and group in SSRS

sql2012
sql2008
#1

Hi,
I want to know how can we check specific user and group what permissions are assigned to reports created in SQL Server Reporting Service.

#2

If I understand your question properly, I'd start with something like this.
SELECT DISTINCT
[Path]
,[Name]
,[Type]
,C.[Description]
,U.[UserName] AS CreatedBy
,UR.UserName AS RoleUser
,STUFF((Select ', ' + R.RoleName
FROM [dbo].[Roles] R JOIN
[dbo].[PolicyUserRole] PUR ON PUR.RoleID = R.RoleID
WHERE PUR.UserID = UR.UserID
AND PUR.PolicyID = '111S11GH1-1111-11DA-1111-111C11A111B1'
FOR XML PATH ('')),1,2,'') AS Roles
FROM [dbo].[Catalog] C JOIN
[dbo].[Users] U ON C.[CreatedByID] = U.[UserID] JOIN
[dbo].[PolicyUserRole] PUR ON C.PolicyID = PUR.PolicyID JOIN
[dbo].[Users] UR ON PUR.UserID = UR.UserID
WHERE C.PolicyID = '111S11GH1-1111-11DA-1111-111C11A111B1'

Each Report is in the Catalog table, and each has a PolicyID. From here it would depend on who needs to see the data how as to what to do next.

Hope this helps-