October 28, 2008 at 1:21 pm
We are trying to make a call to sp_addsrvrolemember within a trigger and are getting the following error:
Msg 15002, Level 16, State 1, Procedure sp_addsrvrolemember, Line 14
The procedure 'sys.sp_addsrvrolemember' cannot be executed within a transaction.
We realize that sql server creates an implicit transaction for triggers but can it be turned off in some manner.
We've tried using SET IMPLICIT_TRANSACTIONS OFF before the call to the sp but it continues to fail.
Is there a workaround so that the sp can be called within the trigger?
October 28, 2008 at 1:34 pm
What type of trigger are you using?
Also why are you trying to do this?
October 28, 2008 at 1:58 pm
INSERT, UPDATE, DELETE Trigger
We have a table with a user's list. Support person has a simple front-end to manage records in this table. The trigger is on this table. Want to be able to capture inserts, updates and deletes on this table and either create or delete related sql server users as necessary. It would be a lot easier if this all happened within the trigger than having to teach a support person the code to run to add a user.
October 28, 2008 at 3:18 pm
Just add the code in the after_insert event and synchronise the logins within the application.
Other option is to add a job that works the logins. Then run that job a couple times an hour... or start the job from within the trigger using raiserror (create a costum error that fires a job when raised).
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply