reporting-services - SSRS 订阅迁移

Question

我建立了多个 SSRS 订阅，但我的公司刚刚进行了域转换，这些订阅都保存到我的旧域登录名中。这导致了一些问题:

1) 有没有办法将所有这些订阅转移到我的新帐户？

2) 是否可以将订阅设置为可供多个用户查看？

3) 是否可以将订阅设置为可由多个用户编辑？

我们正在使用 SSRS 2012

Answer 1

我不得不在我的上一个组织中这样做几次。

为了回答您的第一个问题，我使用下面的脚本来更新订阅和报告所有者。我使用 SQL 命令模式轻松更改为不同的服务器。变量也是必需的。

要在 SSMS 中打开 SQL 命令模式，请在菜单栏上单击 Query | SQLCMD 模式

Note: The NewUser must already exist in dbo.Users and you must have UPDATE permission on the dbo.ReportServer database to use this script.

/*------------------------------------------------------------------------------+
| Purpose:  To Update the owner of deployed reports and subscriptions 
| Note:     SQLCmdMode Script
+--------------------------------------------------------------------------------
*/

:setvar _server "***YourServerNameHere***"
:setvar _database "ReportServer"
:connect $(_server) 
USE [$(_database)];
GO


:SETVAR OldUser "DOMAIN\OldUserName"
:SETVAR NewUser "DOMAIN\NewUserName"

SET XACT_ABORT ON
BEGIN TRANSACTION

PRINT '====================================================================='
PRINT 'Update subscriptions...'
PRINT '====================================================================='

;WITH 
new_owner
AS
(
    SELECT [UserID], [UserName] FROM dbo.[Users] WHERE [UserName] =  N'$(NewUser)'
)
, 
subscription_source
AS
(
    SELECT 
          s.[Report_OID]
        , [OldOwner] = ou.[UserName]
        , [OldOwnerID] = ou.[UserID]
        , [NewOwner] = nu.[UserName]
        , [NewOwnerID] = nu.[UserID]
    FROM 
        dbo.[Subscriptions] AS s
        INNER JOIN dbo.[Users] AS ou ON ou.[UserID] = s.[OwnerID]
        , new_owner AS nu
    WHERE 
        1=1
        AND ou.[UserName] =  N'$(OldUser)'
)
--SELECT * FROM subscription_source
MERGE dbo.Subscriptions AS T
USING subscription_source AS S ON T.[Report_OID] = S.[Report_OID]
WHEN MATCHED 
THEN UPDATE SET 
        T.[OwnerID] = S.[NewOwnerID] 
OUTPUT @@ServerName AS ServerName, db_name() AS DatabaseName, $action, inserted.*, deleted.*; 

PRINT '====================================================================='
PRINT 'Update report created by...'
PRINT '====================================================================='

;WITH 
new_owner
AS
(
    SELECT [UserID], [UserName] FROM dbo.[Users] WHERE [UserName] =  N'$(NewUser)'
)
, 
report_list_source
AS
(
    SELECT 
          c.[ItemID]
        , c.[Name]
        , [OldOwner] = ou.[UserName]
        , [OldOwnerID] = ou.[UserID]
        , [NewOwner] = nu.[UserName]
        , [NewOwnerID] = nu.[UserID]
    FROM 
        dbo.[Catalog] AS c
        INNER JOIN dbo.[Users] AS ou ON ou.[UserID] = c.[CreatedById]
        , new_owner AS nu
    WHERE 
        1=1
        AND ou.[UserName] =  N'$(OldUser)'
        AND c.[Type] = 2
)
--SELECT * FROM report_list_source
MERGE dbo.[Catalog] AS T
USING report_list_source AS S ON T.[ItemID] = S.[ItemID]
WHEN MATCHED 
THEN UPDATE SET 
        T.[CreatedById] = S.[NewOwnerID] 
OUTPUT @@ServerName AS ServerName, db_name() AS DatabaseName, $action, inserted.*, deleted.*; 

PRINT '====================================================================='
PRINT 'Update report modified by...'
PRINT '====================================================================='

;WITH 
new_owner
AS
(
    SELECT [UserID], [UserName] FROM dbo.[Users] WHERE [UserName] =  N'$(NewUser)'
)
, 
report_list_source
AS
(
    SELECT 
          c.[ItemID]
        , c.[Name]
        , [OldOwner] = ou.[UserName]
        , [OldOwnerID] = ou.[UserID]
        , [NewOwner] = nu.[UserName]
        , [NewOwnerID] = nu.[UserID]
    FROM 
        dbo.[Catalog] AS c
        INNER JOIN dbo.[Users] AS ou ON ou.[UserID] = c.[ModifiedById]
        , new_owner AS nu
    WHERE 
        1=1
        AND ou.[UserName] =  N'$(OldUser)'
        AND c.[Type] = 2
)
--SELECT * FROM report_list_source
MERGE dbo.[Catalog] AS T
USING report_list_source AS S ON T.[ItemID] = S.[ItemID]
WHEN MATCHED 
THEN UPDATE SET 
        T.[ModifiedById] = S.[NewOwnerID] 
OUTPUT @@ServerName AS ServerName, db_name() AS DatabaseName, $action, inserted.*, deleted.*; 


PRINT '******* ROLLBACK TRANSACTION ******* ';
ROLLBACK TRANSACTION;

--PRINT '******* COMMIT TRANSACTION ******* ';
--COMMIT TRANSACTION;

PRINT '====================================================================='
PRINT 'Finished...'
PRINT '====================================================================='

为了回答你的第二个问题，我写了一份报告来查看所有订阅。下面是报表SQL。如果您只想下载它，这里是 rdl file。

/*'------------------------------------------------------------------------------------------------------------------
| Purpose:  Schedule Of Recurring Report Subscriptions
| Note:     SQLCmdMode Script
'--------------------------------------------------------------------------------------------------------------------

DECLARE @all_value AS VARCHAR(100)
DECLARE @ReportFolder AS VARCHAR(100)
DECLARE @ReportName AS VARCHAR(100)
DECLARE @EmailLike AS VARCHAR(100)
DECLARE @ModifiedBy AS VARCHAR(50)
DECLARE @SubcriptionOwner AS VARCHAR(50)
DECLARE @SubscriptionStatus AS VARCHAR(1)
DECLARE @EventStatus AS VARCHAR(50)
DECLARE @Current AS VARCHAR(50)
DECLARE @LastSubscriptionDate AS DATETIME

SET @all_value = '<ALL>'
SET @ReportFolder = '<ALL>'
SET @ReportName = '<ALL>'
SET @EmailLike = NULL 
SET @ModifiedBy = NULL
SET @SubcriptionOwner = NULL
SET @SubscriptionStatus = 'A' -- Y=Sent, N=Fail, A=All
SET @EventStatus = '<ALL>'  -- status from ReportServer.dbo.ExecutionLog 
SET @Current = '<ALL>' 
SET @LastSubscriptionDate = NULL --getdate()-1

*/

;WITH
report_users 
AS
(
    SELECT [UserID], [UserName], [SimpleUserName] = UPPER(RIGHT([UserName],(LEN([UserName])-CHARINDEX('\',[UserName])))) FROM dbo.[Users]
)
,
report_catalog
AS
(
    SELECT    
          c.[ItemID]
        , c.[CreatedById]
        , c.[ModifiedById]
        , c.[Type]
        , c.[Name]
        , c.[Description]
        , c.[Parameter]
        , [ReportCreationDate] = CONVERT(DATETIME, CONVERT(VARCHAR(11), c.[CreationDate], 13))
        , [ReportModifiedDate] = CONVERT(DATETIME, CONVERT(VARCHAR(11), c.[ModifiedDate], 13))
        , [ReportFolder] = 
            CASE
                WHEN c.[Path] = '/' + c.[Name] THEN ''
                ELSE SUBSTRING(c.[Path], 2, Len(c.[Path])-Len(c.[Name])-2) 
            END 
        , [ReportPath] = c.[Path]
        , [UrlPath] = 'http://' + Host_Name() + '/Reports/Pages/Folder.aspx?ItemPath=%2f'
        , [ReportDefinition] = CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX), c.[Content]))  
    FROM 
        dbo.[Catalog] AS c
    WHERE c.[Type] = 2
)
, 
subscription_days
AS
(
    SELECT tbl.* FROM (VALUES
      ( 'DaysOfMonth', 1, '1')
    , ( 'DaysOfMonth', 2, '2')
    , ( 'DaysOfMonth', 4, '3')
    , ( 'DaysOfMonth', 8, '4')
    , ( 'DaysOfMonth', 16, '5')
    , ( 'DaysOfMonth', 32, '6')
    , ( 'DaysOfMonth', 64, '7')
    , ( 'DaysOfMonth', 128, '8')
    , ( 'DaysOfMonth', 256, '9')
    , ( 'DaysOfMonth', 512, '10')
    , ( 'DaysOfMonth', 1024, '11')
    , ( 'DaysOfMonth', 2048, '12')
    , ( 'DaysOfMonth', 4096, '13')
    , ( 'DaysOfMonth', 8192, '14')
    , ( 'DaysOfMonth', 16384, '15')
    , ( 'DaysOfMonth', 32768, '16')
    , ( 'DaysOfMonth', 65536, '17')
    , ( 'DaysOfMonth', 131072, '18')
    , ( 'DaysOfMonth', 262144, '19')
    , ( 'DaysOfMonth', 524288, '20')
    , ( 'DaysOfMonth', 1048576, '21')
    , ( 'DaysOfMonth', 2097152, '22')
    , ( 'DaysOfMonth', 4194304, '23')
    , ( 'DaysOfMonth', 8388608, '24')
    , ( 'DaysOfMonth', 16777216, '25')
    , ( 'DaysOfMonth', 33554432, '26')
    , ( 'DaysOfMonth', 67108864, '27')
    , ( 'DaysOfMonth', 134217728, '28')
    , ( 'DaysOfMonth', 268435456, '29')
    , ( 'DaysOfMonth', 536870912, '30')
    , ( 'DaysOfMonth', 1073741824, '31')
    , ( 'DaysOfMonth', 8193, '1st and 14th')
    , ( 'DaysOfWeek', 1, 'Sun')
    , ( 'DaysOfWeek', 2, 'Mon')
    , ( 'DaysOfWeek', 4, 'Tues')
    , ( 'DaysOfWeek', 8, 'Wed')
    , ( 'DaysOfWeek', 16, 'Thurs')
    , ( 'DaysOfWeek', 32, 'Fri')
    , ( 'DaysOfWeek', 64, 'Sat')
    , ( 'DaysOfWeek', 62, 'Mon - Fri')
    , ( 'DaysOfWeek', 10, 'Mon - Wed')
    , ( 'DaysOfWeek', 24, 'Wed - Thurs')
    , ( 'DaysOfWeek', 120, 'Wed - Sat')
    , ( 'DaysOfWeek', 126, 'Mon - Sat')
    , ( 'DaysOfWeek', 127, 'Daily')
    , ( 'DayOfWeek', 1, 'Sun')
    , ( 'DayOfWeek', 127, 'Sun')
    , ( 'DayOfWeek', 2, 'Mon')
    , ( 'DayOfWeek', 10, 'Mon')
    , ( 'DayOfWeek', 62, 'Mon')
    , ( 'DayOfWeek', 126, 'Mon')
    , ( 'DayOfWeek', 127, 'Mon')
    , ( 'DayOfWeek', 4, 'Tue')
    , ( 'DayOfWeek', 10, 'Tue')
    , ( 'DayOfWeek', 62, 'Tue')
    , ( 'DayOfWeek', 126, 'Tue')
    , ( 'DayOfWeek', 127, 'Tue')
    , ( 'DayOfWeek', 8, 'Wed')
    , ( 'DayOfWeek', 10, 'Wed')
    , ( 'DayOfWeek', 24, 'Wed')
    , ( 'DayOfWeek', 62, 'Wed')
    , ( 'DayOfWeek', 120, 'Wed')
    , ( 'DayOfWeek', 126, 'Wed')
    , ( 'DayOfWeek', 127, 'Wed')
    , ( 'DayOfWeek', 16, 'Thr')
    , ( 'DayOfWeek', 24, 'Thr')
    , ( 'DayOfWeek', 62, 'Thr')
    , ( 'DayOfWeek', 120, 'Thr')
    , ( 'DayOfWeek', 126, 'Thr')
    , ( 'DayOfWeek', 127, 'Thr')
    , ( 'DayOfWeek', 32, 'Fri')
    , ( 'DayOfWeek', 62, 'Fri')
    , ( 'DayOfWeek', 120, 'Fri')
    , ( 'DayOfWeek', 126, 'Fri')
    , ( 'DayOfWeek', 127, 'Fri')
    , ( 'DayOfWeek', 64, 'Sat')
    , ( 'DayOfWeek', 120, 'Sat')
    , ( 'DayOfWeek', 126, 'Sat')
    , ( 'DayOfWeek', 127, 'Sat')
    ) tbl ([GroupName], [CodeNbr], [Label]) 
)
,
subscription_schedule
AS
(
    SELECT 
          [ScheduleID]
        , [SchDaySun] = Sun
        , [SchDayMon] = Mon
        , [SchDayTue] = Tue
        , [SchDayWed] = Wed
        , [SchDayThr] = Thr
        , [SchDayFri] = Fri
        , [SchDaySat] = Sat
        , [ScheduleName]
        , [ScheduleStartDate]
        , [ScheduleEndDate] 
        , [Flags]
        , [RecurrenceType]
        , [State]
        , [MinutesInterval]
        , [DaysInterval]
        , [WeeksInterval]
        , [DaysOfWeek]
        , [DaysOfMonth]
        , [Month]
        , [MonthlyWeek] 
        , [ScheduleDays] 
    FROM
        (
        SELECT 
              sc.[ScheduleID]
            , sd.[CodeNbr]
            , sd.[Label]
            , [ScheduleName] = sc.[name]
            , [ScheduleStartDate] = sc.[StartDate]
            , [ScheduleEndDate] = sc.[EndDate]
            , sc.[Flags]
            , sc.[RecurrenceType]
            , sc.[State]
            , sc.[MinutesInterval]
            , sc.[DaysInterval]
            , sc.[WeeksInterval]
            , sc.[DaysOfWeek]
            , sc.[DaysOfMonth]
            , sc.[Month]
            , sc.[MonthlyWeek] 
            , [ScheduleDays] = 
                CASE
                    WHEN sc.[DaysOfMonth] IS NOT NULL THEN COALESCE(dom.[Label], '(' + CAST(sc.[DaysOfMonth] AS VARCHAR(20)) + ') NOT CODED')
                    WHEN sc.[DaysOfWeek] IS NOT NULL THEN COALESCE(dow.[Label], '(' + CAST(sc.[DaysOfWeek] AS VARCHAR(20)) + ') NOT CODED')
                END 
            --, sc.[RecurrenceType]
        FROM 
            dbo.[Schedule] sc 
            LEFT JOIN subscription_days sd ON sc.[DaysOfWeek] = sd.[CodeNbr] AND sd.[GroupName] = 'DayOfWeek'
            LEFT JOIN subscription_days AS dom ON sc.[DaysOfMonth] = dom.[CodeNbr] AND dom.[GroupName] = 'DaysOfMonth'
            LEFT JOIN subscription_days AS dow ON sc.DaysOfWeek = dow.CodeNbr AND dow.[GroupName] = 'DaysOfWeek'
        ) sch
        PIVOT
        (
            COUNT(sch.[Label]) 
            FOR sch.[Label]
            IN ([Sun], [Mon], [Tue], [Wed], [Thr], [Fri], [Sat])
        ) AS pvt
)
,
report_subscription
AS
(
    SELECT     
          s.[SubscriptionID]
        , s.[Report_OID]
        , [SubscriptionDescription] = s.[Description]
        , s.[ExtensionSettings]
        , s.[EventType]
        , s.[OwnerID]
        , s.[ModifiedByID]
        , s.[ModifiedDate]      
        , [RunTime] = CONVERT(VARCHAR(5), s.[LastRunTime], 8)
        , [LastRunDate] = CONVERT(VARCHAR(11),s.[LastRunTime],13)
        , s.[LastRunTime]
        , s.[DeliveryExtension]     
        , s.[MatchData]     
        , [SubscriptionLastStatus] = s.[LastStatus]     
        , [StatusFail] = CASE WHEN s.[LastStatus]  LIKE '%Mail sent%' THEN 'N' ELSE 'Y' END     
        , [EmailSubject] = CASE CHARINDEX('<Name>SUBJECT</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>SUBJECT</Name><Value>') + CHARINDEX('<Name>SUBJECT</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>SUBJECT</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>SUBJECT</Name><Value>') + CHARINDEX('<Name>SUBJECT</Name><Value>', s.ExtensionSettings))) END
        , [EmailTo] = SUBSTRING(s.ExtensionSettings, LEN('<Name>TO</Name><Value>') + CHARINDEX('<Name>TO</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>TO</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>TO</Name><Value>') + CHARINDEX('<Name>TO</Name><Value>', s.ExtensionSettings)))
        , [EmailCc] = CASE CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>CC</Name><Value>') + CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>CC</Name><Value>') + CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings))) END 
        , [EmailBcc] = CASE CHARINDEX('<Name>BCC</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>BCC</Name><Value>') + CHARINDEX('<Name>BCC</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>BCC</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>BCC</Name><Value>') + CHARINDEX('<Name>BCC</Name><Value>', s.ExtensionSettings))) END
        , [EmailComment] = CASE CHARINDEX('<Name>Comment</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>Comment</Name><Value>') + CHARINDEX('<Name>Comment</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>Comment</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>Comment</Name><Value>') + CHARINDEX('<Name>Comment</Name><Value>', s.ExtensionSettings))) END
        , [EmailIncludeLink] = CASE CHARINDEX('<Name>IncludeLink</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>IncludeLink</Name><Value>') + CHARINDEX('<Name>IncludeLink</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>IncludeLink</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>IncludeLink</Name><Value>') + CHARINDEX('<Name>IncludeLink</Name><Value>', s.ExtensionSettings))) END 
        , [EmailRenderFormat] = CASE CHARINDEX('<Name>RenderFormat</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>RenderFormat</Name><Value>') + CHARINDEX('<Name>RenderFormat</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>RenderFormat</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>RenderFormat</Name><Value>') + CHARINDEX('<Name>RenderFormat</Name><Value>', s.ExtensionSettings))) END
        , [EmailPriority] = CASE CHARINDEX('<Name>Priority</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>Priority</Name><Value>') + CHARINDEX('<Name>Priority</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>Priority</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>Priority</Name><Value>') + CHARINDEX('<Name>Priority</Name><Value>', s.ExtensionSettings))) END
        , sch.[MinutesInterval]
        , sch.[DaysInterval]
        , sch.[WeeksInterval]
        , sch.[DaysOfWeek]
        , sch.[DaysOfMonth]
        , sch.[Month]
        , sch.[MonthlyWeek] 
        --, [JobName] = sj.[name] 
        , sch.[ScheduleName]
        , sch.[ScheduleDays] 
        , sch.[SchDaySun]
        , sch.[SchDayMon]
        , sch.[SchDayTue]
        , sch.[SchDayWed]
        , sch.[SchDayThr]
        , sch.[SchDayFri]
        , sch.[SchDaySat]
        , sch.[ScheduleStartDate] 
        , sch.[ScheduleEndDate] 
        , sch.[Flags]
        , sch.[RecurrenceType]
        , sch.[State]
    FROM  
        dbo.[Subscriptions] AS s 
        LEFT JOIN dbo.[Notifications] AS n ON n.[SubscriptionID] = s.[SubscriptionID] AND s.[Report_OID] = n.[ReportID]
        LEFT JOIN dbo.[ReportSchedule] AS rs ON s.[SubscriptionID] = rs.[SubscriptionID]
        --LEFT JOIN MSDB.dbo.[sysjobs] AS sj ON sj.[name] = CAST(rs.[ScheduleID] AS VARCHAR(255))
        LEFT JOIN subscription_schedule AS sch ON rs.[ScheduleID] = sch.[ScheduleID] 
    WHERE 
        1=1
        --AND sch.[RecurrenceType] IN(4,5) -- 1 = is one off, 4 = daily, 5 = monthly
        --AND s.EventType = 'TimedSubscription' 
)
SELECT     
      c.[Name] 
    , c.[Description]
    , c.[Parameter]     
    , c.[ReportFolder] 
    , c.[ReportPath] 
    , [URL_ReportFolder] = c.[UrlPath] + c.[ReportFolder] + '&ViewMode=List'
    , [URL_Report] = c.[UrlPath] + c.[ReportFolder] + '%2f' + c.Name 
    , [URL] = 'http://' + Host_Name() + '/Reports/Pages/SubscriptionProperties.aspx?ItemPath=' + c.ReportPath + '&IsDataDriven=False&SubscriptionID=' + CAST(s.SubscriptionID AS VARCHAR(80)) 
    , [URL2] = 'http://' + Host_Name() + '/Reports/Pages/Report.aspx?ItemPath=' + c.[ReportPath] + '&SelectedTabId=SubscriptionsTab'  
    , [ReportCreatedBy] = urc.[SimpleUserName]
    , c.[ReportCreationDate]
    , [ReportModifiedBy] = urm.[SimpleUserName]
    , c.[ReportModifiedDate]    
    , [SubscriptionOwner] = usc.[SimpleUserName]
    , [SubscriptionModifiedBy] = usm.[SimpleUserName]
    , [SubscriptionModifiedDate] = s.[ModifiedDate]     
    , s.[SubscriptionID]
    , s.[SubscriptionDescription] 
    , s.[ExtensionSettings]
    , s.[EventType]
    , s.[EmailSubject] 
    , s.[EmailTo]
    , s.[EmailCc]
    , s.[EmailBcc] 
    , s.[EmailComment]
    , s.[EmailIncludeLink] 
    , s.[EmailRenderFormat]
    , s.[EmailPriority] 
    , s.[DeliveryExtension] 
    , s.[SubscriptionLastStatus]
    , s.[StatusFail] 
    , s.[MatchData] 
    , s.[RunTime] 
    , s.[LastRunDate]
    , s.[LastRunTime]   
    , s.[MinutesInterval]
    , s.[DaysInterval]
    , s.[WeeksInterval]
    , s.[DaysOfWeek]
    , s.[DaysOfMonth]
    , s.[Month]
    , s.[MonthlyWeek]   
    , [JobName] = NULL  --, s.[JobName]     
    , s.[ScheduleName] 
    , s.[ScheduleDays]
    , s.[SchDaySun]
    , s.[SchDayMon]
    , s.[SchDayTue]
    , s.[SchDayWed]
    , s.[SchDayThr]
    , s.[SchDayFri]
    , s.[SchDaySat]
    , s.[ScheduleStartDate]
    , s.[ScheduleEndDate]
    , s.[Flags]
    , s.[RecurrenceType]
    , s.[State]
    , [EventStatus] = el.[Status]
    , [EventDateTime] = el.[TimeEnd] 
FROM  
    report_catalog AS c
    INNER JOIN report_subscription AS s ON s.[Report_OID] = c.[ItemID] 
    LEFT OUTER JOIN (SELECT b.[ReportID], b.[Status], b.[TimeEnd]
                    FROM dbo.[ExecutionLog] b 
                    INNER JOIN (SELECT [ReportID], MAX([TimeEnd]) AS [TimeEnd]
                                FROM dbo.[ExecutionLog] 
                                GROUP BY [ReportID]) a ON b.[ReportID] = a.[ReportID] AND b.[TimeEnd] = a.[TimeEnd]
                    )AS el ON el.[ReportID] = c.[ItemID]
    LEFT OUTER JOIN report_users AS urc ON c.[CreatedById] = urc.[UserID]
    LEFT OUTER JOIN report_users AS urm ON c.[ModifiedById] = urm.[UserID] 
    LEFT OUTER JOIN report_users AS usc ON s.[OwnerID] = usc.[UserID] 
    LEFT OUTER JOIN report_users AS usm ON s.[ModifiedByID] = usm.[UserID] 
WHERE 
    1=1
    AND c.[Type] = 2
    AND (@all_value IN (@ReportFolder) OR c.[ReportFolder] IN(@ReportFolder))
    AND (@all_value IN (@ReportFolder) OR CHARINDEX(@ReportFolder, c.[ReportPath]) > 0)
    AND (@all_value IN(@ReportName) OR c.[Name] IN(@ReportName))
    AND (@all_value IN(@EventStatus) OR el.[Status] IN(@EventStatus))
    AND (@all_value IN(@Current) OR CASE WHEN s.[ScheduleEndDate] IS NULL THEN 'Current' WHEN s.[ScheduleEndDate] IS NOT NULL THEN 'Non Current' END = @Current)
    AND (@all_value IN(@SubscriptionStatus) OR s.[SubscriptionLastStatus] LIKE '%' + @SubscriptionStatus + '%')
    AND (s.[LastRunTime] >= @LastSubscriptionDate OR @LastSubscriptionDate IS NULL) 
    AND 
        (
            (SUBSTRING(s.[ExtensionSettings], LEN('<Name>TO</Name><Value>') + CHARINDEX('<Name>TO</Name><Value>', s.[ExtensionSettings]), CHARINDEX('</Value>', s.[ExtensionSettings], CHARINDEX('<Name>TO</Name><Value>', s.[ExtensionSettings]) + 1) - (LEN('<Name>TO</Name><Value>') + CHARINDEX('<Name>TO</Name><Value>', s.[ExtensionSettings])))
            LIKE '%' + @EmailLike + '%' OR @EmailLike IS NULL
        )
        OR
        ( 
            CASE CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings) 
                WHEN 0 THEN '' 
                ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>CC</Name><Value>') + CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>CC</Name><Value>') + CHARINDEX('<Name>CC</Name><Value>', s.[ExtensionSettings]))) 
            END 
        LIKE '%' + @EmailLike + '%'
        )
        OR 
        (
            CASE CHARINDEX('<Name>BCC</Name><Value>', s.[ExtensionSettings]) 
                WHEN 0 THEN '' 
                ELSE SUBSTRING(s.[ExtensionSettings], LEN('<Name>BCC</Name><Value>') + CHARINDEX('<Name>BCC</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.[ExtensionSettings], CHARINDEX('<Name>BCC</Name><Value>', s.[ExtensionSettings]) + 1) - (LEN('<Name>BCC</Name><Value>') + CHARINDEX('<Name>BCC</Name><Value>', s.[ExtensionSettings]))) 
            END 
        LIKE '%' + @EmailLike + '%')
        )

为了回答您的第三个问题，我会使用报告文件夹中的角色来管理权限。

在 SSRS 中，新角色和对现有角色的调整必须在 SQL Server Management Studio、SSMS 中执行。打开 SSMS 后，单击“连接”并选择“Reporting Services…”

输入您的服务器名称和登录信息，然后单击“连接”。

连接到报表服务器后，打开安全文件夹，右键单击“角色”，然后单击“新建角色...”

您可以创建一个新角色订阅编辑或职能所有者，然后您可以根据您希望用户成为的角色为新角色分配权限能够做到。

然后在报告管理器上单击文件夹的向下箭头并选择“安全”

然后点击“新建角色分配”

然后输入 Active Directory 组或电子邮件地址并检查您创建的新角色。

这是我为 MSDN 上的报告服务器权限编写的更详细的 wiki。