Executing Remote Stored Procedures in Triggers

Hi,

I've been scratching my head over this problem for quite a while. I have two SQL SERVER 2005 servers running on the same network, lets call them ServA and ServB. ServB is configured as a linked server on ServA as LinkedServB. On ServA, there is a database called DatabaseA, in which there is a table called TableAA, on which I wrote a trigger on delete.

In that trigger, I want to update a table, lets call it TableBB, in DatabaseB on ServB.

So the trigger looks like this:

CREATE TRIGGER triggerAfterDelete
ON TableAA
AFTER DELETE
AS
BEGIN

SET NOCOUNT ON;

Update [LinkedServB].[DatabaseB].[dbo].[TableBB]
set [SomeColumn] = 'SomeValue'
where [SomeOtherColumn] = 'SomeOtherValue'

END

When I delete something from TableAA, the trigger fires, and on trying to update, an error is raised which is:

Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Now the same query works as a separate query. But inside the trigger it does not. I've tried to use try-catches, nested transaction, named transaction, saving transactions, distributed tran, checking @.@.error, running the query using openquery, running the query using sp_executesql, but they have all given me some error or other.

Selects in the trigger work fine, but updates, inserts and deletes do not work.
And like I mentioned, as a stand alone query, they work fine, as a query, in openquery, or in sp_executesql

Any help would be much appreciated.

Thanks in Advance

Vinit Pandya
Senior .NET Developer

You need to SET XACT_ABORT ON to avoid the error. Otherwise the provider has to support nested transactions for this to work and SQL Server does not support nested transactions. For details on the behavior of distributed queries in transactions, see the BOL topic below:

http://msdn2.microsoft.com/en-us/library/aa213080(SQL.80).aspx

|||Hi Umachandar,

Thanks for your reply. I've actually tried that too, and also other settings which I read helped other people such as:

SET XACT_ABORT ON;
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

But still no luck. Any other ideas ?

Thanks.

Vinit Pandya
Senior .NET Developer

Execute trigger as a specific user

Hi,
Within my database I have some triggers on the tables (in database a)
which execute stored procedures and alter data in a separate database
(database b).
I have a security issue here as when ever I alter the tables in
database a, I have to make sure that the user exists in database b. If
not, the transaction will fail.
Does anyone know if it is possible to force a trigger to execute a
specified user? This would allow me to hardcode the user in the
trigger in database a to use a username with is already established
within database b....
Any suggestions would be great as I've been struggling with this for a
long long time.
All the best
AllanThere is no 'execute as' functionality in SQL 2000. A user must have a
security context in the other database in order to access objects therein.
If you don't want to add the user to databaseB too, an alternative is to
enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
that have not been granted access to databaseB explicitly can then access
the database using the guest user and are limited to those permissions
granted to the guest.user or public role.
However, you probably don't want to grant object permissions to public or
guest. In this case, you can enable 'db chaining' in both databases. This
will honor cross-database ownership chaining so that permissions are not
needed on objects in databaseB referenced by your proc as long as all
objects are owned by the same user. If your objects are owned by 'dbo',
both databases also need to be owned by the same login so that the 'dbo'
user ownership chain is unbroken..
Note that you should enable cross-database chaining only if you fully
understand the security implications. You need to fully trust users that
have permissions to create dbo-owned objects in those databases. Never
enable cross-database chaining in an sa-owned database unless only symin
role members can create dbo-owned objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan Martin" <allanmartin@.ntlworld.com> wrote in message
news:a6d765d6.0502050820.70385c2e@.posting.google.com...
> Hi,
> Within my database I have some triggers on the tables (in database a)
> which execute stored procedures and alter data in a separate database
> (database b).
> I have a security issue here as when ever I alter the tables in
> database a, I have to make sure that the user exists in database b. If
> not, the transaction will fail.
> Does anyone know if it is possible to force a trigger to execute a
> specified user? This would allow me to hardcode the user in the
> trigger in database a to use a username with is already established
> within database b....
> Any suggestions would be great as I've been struggling with this for a
> long long time.
> All the best
> Allan|||fantastic... this section worked for me. Thanks very very much.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
Allan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<e5fX4#6CFHA.2180
@.TK2MSFTNGP10.phx.gbl>...
> There is no 'execute as' functionality in SQL 2000. A user must have a
> security context in the other database in order to access objects therein.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
> However, you probably don't want to grant object permissions to public or
> guest. In this case, you can enable 'db chaining' in both databases. Thi
s
> will honor cross-database ownership chaining so that permissions are not
> needed on objects in databaseB referenced by your proc as long as all
> objects are owned by the same user. If your objects are owned by 'dbo',
> both databases also need to be owned by the same login so that the 'dbo'
> user ownership chain is unbroken..
> Note that you should enable cross-database chaining only if you fully
> understand the security implications. You need to fully trust users that
> have permissions to create dbo-owned objects in those databases. Never
> enable cross-database chaining in an sa-owned database unless only symi
n
> role members can create dbo-owned objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> news:a6d765d6.0502050820.70385c2e@.posting.google.com...|||I'm glad it help you out.
Dan Guzman
SQL Server MVP
"Allan Martin" <allan.martin@.gmail.com> wrote in message
news:7ef7970c.0502090104.7d4c2781@.posting.google.com...
> fantastic... this section worked for me. Thanks very very much.
> Allan
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:<e5fX4#6CFHA.2180@.TK2MSFTNGP10.phx.gbl>...