Report Server 报表用户权限

查询用户在报表上的权限

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

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