SQL Server Report Server 报表用户权限T-SQL查询

/**************************************************************************

查询用户在报表上的权限

**************************************************************************/

use ReportServer

go

SELECT distinct c.Path,c.Name,d.UserName,b.RoleName
FROM [dbo].[PolicyUserRole] a, [dbo].[Roles] b, [dbo].[Catalog] c, users d
where a.RoleID=b.RoleID
and a.PolicyID=c.PolicyID
and a.UserID=d.UserID

/**************************************************************************

查询报表订阅Owner和对应的SQL Job

**************************************************************************/

select 'ReportName' = c.[name],
'ReportPath' = c.Path,
'SubnDesc' = s.Description,
'SubnOwner' = us.UserName,
'LastStatus' = s.LastStatus,
'LastRun' = s.LastRunTime,
'ReportModifiedBy' = uc.UserName,
'SubscriptionJobName' = j.name
from dbo.Subscriptions s
join dbo.Catalog c on c.ItemID = s.Report_OID and convert(varchar(10),s.LastRunTime,120) = convert(varchar(10),getdate(),120)
join dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID
join dbo.Users uc on uc.UserID = c.ModifiedByID
join dbo.Users us on us.UserID = s.OwnerId
join msdb.dbo.sysjobs j on j.name = CONVERT(nvarchar(128),rs.ScheduleId)
order by LastRun desc

/**************************************************************************

查询报表元素类型

**************************************************************************/

SELECT Catalog.Path, Catalog.Name, Users.UserName, Catalog.Type,

CASE
WHEN Catalog.type = 1 THEN '1-Folder'
WHEN Catalog.type = 2 THEN '2-Report'
WHEN Catalog.type = 3 THEN '3-File'
WHEN Catalog.type = 4 THEN '4-Linked Report'
WHEN Catalog.type = 5 THEN '5-Datasource'
WHEN Catalog.type = 6 THEN '6-Model'
WHEN Catalog.type = 8 THEN '8-Shared Dataset'
WHEN Catalog.type = 9 THEN '9-Report Part'
WHEN Catalog.type = 11 THEN 'KPI'
WHEN Catalog.type = 12 THEN 'Mobile Report (folder)'
WHEN Catalog.type = 13 THEN 'Power BI Desktop Document'
ELSE 'Unknown' END AS [ItemType]
FROM Catalog INNER JOIN
Policies ON Catalog.PolicyID = Policies.PolicyID INNER JOIN
PolicyUserRole ON PolicyUserRole.PolicyID = Policies.PolicyID INNER JOIN
Users ON PolicyUserRole.UserID = Users.UserID
ORDER BY Catalog.Path