SSRS Permissions – Executing Subscription

permissionsssrsssrs-2008

I'd like to execute an SSRS subscription after some ETL work run by a SSIS package that takes a varying amount of time completes. I'm considering using this method, which basically consists of SSIS calling the same stored procedure that the SQL Server Agent jobs for subscriptions call to execute a subscription.

However, I'm not sure what the best way of managing permissions to execute the subscription would be. For example, I'm reluctant to add users to the RsExecRole because it's discouraged by Microsoft in some documents, and I'm reluctant to grant object-level permissions to execute the ReportServer.ado.AddEvent procedure because they'll likely get overlooked in any migrations. (Edit: I suppose we could work around this by having something execute the Agent job instead of calling the stored proc.)

I'm also hoping to avoid issues with the the code that executes the subscription breaking when one of the GUIDs changes.

How do SSRS professionals manage permissions for this type of request? Alternatively, is there a simpler method that bypasses these issues?

Best Answer

If you set up a report subscription with a One-time Schedule and gather its Schedule ID, you can execute it by using sp_start_job. SSRS manages subscriptions with SQL Server Agent, which makes this possible without needing to use Reporting Server Events. The executing user, I imagine a service account, will then only need to be a part of the SQLAgentOperatorRole. See: sp_start_job#permissions.
You allude to this in an edit in you question, but I'd like to outline the process in case anyone else needs a specific solution.


Setting up the schedule is easy. The default time is 2am on the day you set up the subscription schedule and has likely already passed, so you can leave it. Subscription Edit Page, Select Schedule


Then you can gather your Schedule ID by querying the Report Server database tables:

select C.Name ReportName,
    S.Name ScheduleName,
    SU.Description SubscriptionName,
    S.ScheduleID
from ssrs.Catalog C
join ssrs.ReportSchedule RS
    on C.itemID = RS.ReportID
join ssrs.Schedule S
    on RS.scheduleID = S.ScheduleID
join ssrs.Subscriptions SU
    on RS.SubscriptionID = SU.SubscriptionID
where C.name = 'Test Report'

(1 row(s) affected)


Report Server Database Tables


You can kick off the subscription by referencing the job name matching your Schedule ID:

SQL Job 


/*Run Subscription*/
exec msdb.dbo.sp_start_job '8037A345-9F7C-45B0-99FB-B110996BD521'

(1 row(s) affected)
Job '8037A345-9F7C-45B0-99FB-B110996BD521' started successfully.





I have not yet had an issue with the a Schedule ID (GUID) changing and breaking the execution, but as a fail-safe you can create a data-driven subscription and explicitly state a unique description.

Data-Driven Subscription Step 1

You can then reference the description to gather your Schedule ID dynamically.

declare @ScheduleID uniqueidentifier

select @ScheduleID = S.ScheduleID
from ssrs.Catalog C
join ssrs.ReportSchedule RS
    on C.itemID = RS.ReportID
join ssrs.Schedule S
    on RS.scheduleid = S.ScheduleID
join ssrs.Subscriptions SU
    on RS.SubscriptionID = SU.SubscriptionID
where C.name = 'Test Report'
    and SU.Description = 'My Test Report Subscription'

exec msdb.dbo.sp_start_job @ScheduleID

(1 row(s) affected)
Job '8037A345-9F7C-45B0-99FB-B110996BD521' started successfully.
Related Question