Exec procedure from trigger
Posted on 2012-08-28
There are a few SSRS reports (based on data in MS SQL DB1) on ReportServer and my task is to make sure that every time when new data is inserted into DB1 users that have subscription receive a refreshed report. As far as I understand in SQL Server 2012 there are Data Alerts, but I'm still using 2008 R2 version so I have to find another way.
So I'm trying to develop a trigger on DB1 that executes procedure AddEvent from ReportServer DB (DB2) every time new data is inserted into certain table in DB1. Executing procedure AddEvent with certain parameters should make SQL Server send users the latest version of the report.
Simple version of my trigger looks like:
CREATE TRIGGER TriggerName ON DB1
with execute as 'UserName'
if exists (select * from inserted
exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData= 'somedata'
Every time I insert data into DB1 I get the following error:
The server principal "UserName" is not able to access the database "ReportServer" under the current security context.
There are users 'UserName' in both DB1 and DB2 having login 'UserName' associated with them.
Login - public, sysadmin
User in DB1 - db_owner
User in ReportServer DB (DB2) - db_owner, RSExecRole (report server role)
I've enabled cross database ownership chainging for both DB1 and DB2.
It should be quite a simple task: create a trigger that executes a proc from another DB but it does not work.
Can someone please help me?