Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Exec procedure from trigger

Posted on 2012-08-28
23
Medium Priority
?
1,519 Views
Last Modified: 2012-09-02
Hi,
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'
AFTER INSERT
AS
if exists (select * from inserted
)
begin
exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData= 'somedata'
end
GO

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.
Roles:
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?
0
Comment
Question by:lynq
  • 13
  • 8
  • 2
23 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38339983
On your SQL Server instance there should be a database named ReportServer<machinename> which is used by SSRS - make sure the relevant profile under which the trigger is executing has permissions to this database.

That is what the error message is indicating - >> not able to access the database "ReportServer" <<
0
 

Author Comment

by:lynq
ID: 38340183
As I mentioned before there is DB named 'ReportServer', it contains procedure that should be executed from a trigger.
I understand what error message means, but I don't understand why my user can't access ReportServer DB. This user has db_owner role on both databases and has login with same name and sysadmin rights associated with him.
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38340200
Grant execute permissions to your user on the ReportServer database
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:lynq
ID: 38340333
My user is a member of db_owner role.
As far as I know db_owner can perform all configuration and maintenance activities on the database.
http://msdn.microsoft.com/en-us/library/ms180977(v=sql.90).aspx
Is granting additional exec permissions really nessessary?

If so I've already given permissions to exec AddEvent procedure from ReportServer to my user and it hasn't worked
0
 

Author Comment

by:lynq
ID: 38340343
I think the problem is not about executing permissions, user can't even access the ReportServer DB
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38340434
Try giving the user Connect permission to the Report Server DB
0
 

Author Comment

by:lynq
ID: 38340445
Can you please specify how?
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38340459
Right click on the Report Server DB in SSMS
Choose Properties
Select Permissions page
Search for <youruser> and add to 'Users or Roles' list
In the Permissions list scroll down and find the 'Connect' permission and then check on the Grant check box
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 38340464
USE ReportServer;
GRANT EXECUTE TO UserName;
GO

(ref: http://msdn.microsoft.com/en-us/library/ms178569.aspx)
0
 

Author Comment

by:lynq
ID: 38340474
This user already has all permissions from that list. It's strange but it doesn't work either.
0
 

Author Comment

by:lynq
ID: 38340476
2 DepKing

I've already tried that
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38340495
Log into SSMS with the user in question and try and access the Report Server database and let us know what message is returned
0
 

Author Comment

by:lynq
ID: 38340564
I logged into SSMS using that user and it all worked fine. I executed AddEvent procedure manually (not from the trigger) and it send me a report.
0
 

Author Comment

by:lynq
ID: 38340610
Can it be caused by ''with execute as'' statement?
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38340623
Please let us know the results of the following in your circumstance
SELECT [name], value  
FROM [sys].configurations
WHERE [name] = 'cross db ownership chaining';


SELECT [name] AS [Database], [is_db_chaining_on]
FROM [sys].databases
ORDER BY [name];
0
 

Author Comment

by:lynq
ID: 38340650
As written in my first post:
<<I've enabled cross database ownership chainging for both DB1 and DB2.

Ownership chainging at server level is disabled.

Results of your query:

name      value
cross db ownership chaining      0

Database      is_db_chaining_on
DB1         1
master      1
model      0
msdb      1
ReportServer      1
ReportServerTempDB      0
RS      0
RSTempDB      0
tempdb      1
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38340659
Try logging into SSMS with the user in question and access some object in ReportServerTempDB and let us know what happens
0
 

Author Comment

by:lynq
ID: 38340680
Logged into ReportServerTempDB,  selected data from two tables, created simple proc, executed it, dropped it. All worked ok.
0
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 2000 total points
ID: 38340694
As a test switch on CrossDB Ownership chaining at Server level and try the trigger
0
 

Author Comment

by:lynq
ID: 38340758
We are talking about live server which has much more DB than I posted in results of your query.
Cross DB ownership chaining is enabled for both DB, why do I have to enable it on entire server?
0
 

Accepted Solution

by:
lynq earned 0 total points
ID: 38341048
I found the solution here
http://dba.stackexchange.com/questions/15420/trouble-with-database-permissions-for-sp-send-mail/15424#15424
There are two ways to make it work
1. the easy and not secure way: mark the current database as TRUSTWORTHY
ALTER DATABASE [...] SET TRUSTWORTHY ON;
2. the correct way out: use code signing

2 BCUNNEY,
Thank you for your help!
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 38343718
Thanks for posting the answer, lynq.
0
 

Author Closing Comment

by:lynq
ID: 38358414
Found it myself, BCUNNEY helped!
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question