September 23, 2008 at 8:23 am
We have officially began our migration from 2000 to 2005. One task I've been given is to pull a list of current users/roles and folders within Reporting Svcs they have access to. I'm trying to find the easiest way to do this and I'm thinking from Mgmt Studio console I could run a query to give me such list, or I could connect to the Report Server and look this up too, but I don't see a, per say, "security folder" which would house all this information. There are folders which contain dept-related reports.
Is there a standard or generic way to do this, or do I need to look up permissions in every folder?
thx,
John
September 23, 2008 at 10:34 am
John,
This query of the ReportServer database on your server should get you started:
select DISTINCT
c.name as ItemName,
Rolename AS 'Role',
u.username AS 'User',
CASE c.type WHEN 1 THEN 'Folder' ELSE 'Report' END AS Item,
c.path AS Path
from dbo.Users u
join dbo.PolicyUserRole pur
on u.userid = pur.userid
join dbo.Roles r
on pur.roleid = r.roleid
join dbo.Catalog c
on pur.policyid = c.policyid
where
u.username not like '%builtin%'
and
substring(stuff(c.path,1,1,''), 1, charindex('/', substring(c.path, 3, 100),1)) <> 'Shared Datasource'
and
c.name <> 'Shared Datasource'
order by u.username,
itemname,
rolename
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply