May 21, 2010 at 2:03 pm
I have 2 databases, db1 and db2.
In db1 I have table called t1, and in db2 I have a table called t2.
There is an insert trigger on t1 that will insert something in t2.
I have a user in db1. He is not a user on db2. He inserts something into t1, which causes the trigger to fire, but the trigger fails because the user has no access to write to db2.
I would like this trigger to execute without having to give the user any access to db2.
Someone suggested this solution: Activate "guest" user in db2 and grant insert on t2 for "guest".
When I do this, yes, the trigger now works, but the user can also directly insert into db2's t2. I only want the insert in t2 to come from the trigger.
May 21, 2010 at 7:08 pm
Try this... create a SQL login that has access to both databases. Give it specific access to what it needs in both database #1 and database #2. You can even disable the SQL login so no one can use it to log on to SQL Server. Then recreate your trigger using an EXECUTE AS LOGIN clause.
K. Brian Kelley
@kbriankelley
May 24, 2010 at 7:15 am
I read your post earlier today, and I am now able to try your suggestion. However, I am still getting an error. Here is a script of what I tried. Hopefully, the script is self explanatory, but here is what I'm doing.
Login as some sysadmin id. Create 2 databases. Each database has a user created from 2 different logins. Each user gets db_owner role in it's database. In each database, I create a table. In one table, I create a trigger to insert something into the other database's table with execute as user on that database.
create database db1
go
create database db2
go
create login db1_user with password = 'Qazwsx1234'
go
create login db2_user with password = 'Qazwsx1234'
go
use db1
go
create user db1_user from login db1_user
go
sp_addrolemember 'db_owner', 'db1_user'
go
create table t1(c1 int)
go
use db2
go
create user db2_user from login db2_user
go
sp_addrolemember 'db_owner', 'db2_user'
go
create table t2(c1 int)
go
use db1
go
create trigger trig on dbo.t1 with execute as 'db2_user'
after insert, update as
insert into db2.dbo.t2 values(1)
go
Next, login as db_user1 and run
use db1
go
insert into t1 values(1)
go
When I do this I get an error that says db2_user is not able to access db1. Why does db2_user need access to db1? In any case I tried giving db2_user access to db1, but then I get another error saying db2_user is not able to access db2? WHAT? db2_user is db_owner on db2. This is very confusing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply