SSRS subscription migration

Question

I have several SSRS subscriptions built but my company just did a domain conversion and those subscriptions are all saved to my old domain login. This had led to a few questions:

1) Is there a way to move all those subscriptions to my new account?

2) Is it possible to setup the subscriptions to be viewable by multiple users?

3) Is it possible to setup the subscriptions to be editable by multiple users?

We are using SSRS 2012

aduguid · Accepted Answer

I had to do this a few times at my last organization.

To answer your first question, I use the script below to update the subscription and report owners. I use SQL Command Mode to easily change to different servers. It is also necessary for the variables.

To turn on SQL Command Mode in SSMS, on the menu bar click on Query | SQLCMD Mode

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 '====================================================================='

To answer your second question, I wrote a report to view all the subscriptions. Below is the report SQL. Here is the rdl file if you just want to download that.

/*'------------------------------------------------------------------------------------------------------------------
| 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 = ''
SET @ReportFolder = ''
SET @ReportName = ''
SET @EmailLike = NULL 
SET @ModifiedBy = NULL
SET @SubcriptionOwner = NULL
SET @SubscriptionStatus = 'A' -- Y=Sent, N=Fail, A=All
SET @EventStatus = ''  -- status from ReportServer.dbo.ExecutionLog 
SET @Current = '' 
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('SUBJECT', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('SUBJECT') + CHARINDEX('SUBJECT', s.ExtensionSettings), CHARINDEX('', s.ExtensionSettings, CHARINDEX('SUBJECT', s.ExtensionSettings) + 1) - (LEN('SUBJECT') + CHARINDEX('SUBJECT', s.ExtensionSettings))) END
        , [EmailTo] = SUBSTRING(s.ExtensionSettings, LEN('TO') + CHARINDEX('TO', s.ExtensionSettings), CHARINDEX('', s.ExtensionSettings, CHARINDEX('TO', s.ExtensionSettings) + 1) - (LEN('TO') + CHARINDEX('TO', s.ExtensionSettings)))
        , [EmailCc] = CASE CHARINDEX('CC', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('CC') + CHARINDEX('CC', s.ExtensionSettings), CHARINDEX('', s.ExtensionSettings, CHARINDEX('CC', s.ExtensionSettings) + 1) - (LEN('CC') + CHARINDEX('CC', s.ExtensionSettings))) END 
        , [EmailBcc] = CASE CHARINDEX('BCC', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('BCC') + CHARINDEX('BCC', s.ExtensionSettings), CHARINDEX('', s.ExtensionSettings, CHARINDEX('BCC', s.ExtensionSettings) + 1) - (LEN('BCC') + CHARINDEX('BCC', s.ExtensionSettings))) END
        , [EmailComment] = CASE CHARINDEX('Comment', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('Comment') + CHARINDEX('Comment', s.ExtensionSettings), CHARINDEX('', s.ExtensionSettings, CHARINDEX('Comment', s.ExtensionSettings) + 1) - (LEN('Comment') + CHARINDEX('Comment', s.ExtensionSettings))) END
        , [EmailIncludeLink] = CASE CHARINDEX('IncludeLink', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('IncludeLink') + CHARINDEX('IncludeLink', s.ExtensionSettings), CHARINDEX('', s.ExtensionSettings, CHARINDEX('IncludeLink', s.ExtensionSettings) + 1) - (LEN('IncludeLink') + CHARINDEX('IncludeLink', s.ExtensionSettings))) END 
        , [EmailRenderFormat] = CASE CHARINDEX('RenderFormat', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('RenderFormat') + CHARINDEX('RenderFormat', s.ExtensionSettings), CHARINDEX('', s.ExtensionSettings, CHARINDEX('RenderFormat', s.ExtensionSettings) + 1) - (LEN('RenderFormat') + CHARINDEX('RenderFormat', s.ExtensionSettings))) END
        , [EmailPriority] = CASE CHARINDEX('Priority', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('Priority') + CHARINDEX('Priority', s.ExtensionSettings), CHARINDEX('', s.ExtensionSettings, CHARINDEX('Priority', s.ExtensionSettings) + 1) - (LEN('Priority') + CHARINDEX('Priority', 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('TO') + CHARINDEX('TO', s.[ExtensionSettings]), CHARINDEX('', s.[ExtensionSettings], CHARINDEX('TO', s.[ExtensionSettings]) + 1) - (LEN('TO') + CHARINDEX('TO', s.[ExtensionSettings])))
            LIKE '%' + @EmailLike + '%' OR @EmailLike IS NULL
        )
        OR
        ( 
            CASE CHARINDEX('CC', s.ExtensionSettings) 
                WHEN 0 THEN '' 
                ELSE SUBSTRING(s.ExtensionSettings, LEN('CC') + CHARINDEX('CC', s.ExtensionSettings), CHARINDEX('', s.ExtensionSettings, CHARINDEX('CC', s.ExtensionSettings) + 1) - (LEN('CC') + CHARINDEX('CC', s.[ExtensionSettings]))) 
            END 
        LIKE '%' + @EmailLike + '%'
        )
        OR 
        (
            CASE CHARINDEX('BCC', s.[ExtensionSettings]) 
                WHEN 0 THEN '' 
                ELSE SUBSTRING(s.[ExtensionSettings], LEN('BCC') + CHARINDEX('BCC', s.ExtensionSettings), CHARINDEX('', s.[ExtensionSettings], CHARINDEX('BCC', s.[ExtensionSettings]) + 1) - (LEN('BCC') + CHARINDEX('BCC', s.[ExtensionSettings]))) 
            END 
        LIKE '%' + @EmailLike + '%')
        )

To answer your third question, I would use roles in the report folders to manage permissions.

In SSRS, new roles and adjustments to existing roles must be performed in SQL Server Management studio, SSMS. After opening up SSMS, click on "Connect" and select "Reporting Services…"

Enter your Server Name and login information and then click Connect.

After connecting to the Report Server, open the Security Folder right click on "Roles" and click on "New Role…"

You can create a new role Subscription Editor or Functional Owner and then you can assign permissions to the new roles based on what you want the user(s) to be able to do.

Then on the report manager click on the down arrow for a folder and select "Security"

Then click "New Role Assignment"

Then enter the Active Directory group or an email address and check the new role you created.

Here is a more detailed wiki I wrote for report server permission on MSDN.

SSRS subscription migration

Answers (1)

Related Questions