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?