the procedure 'sp_addrolemember' cannot be executed within a transaction

msaeida


I use sp_addrolemember inside a DML trigger and every thing goes well in my computer; I can insert or update into my table and sp_addrolemember is executed successfully, but my customer get this error every time he tries to insert or update into the table.
"the procedure 'sp_addrolemember' cannot be executed within a transaction"

Can anybody help me solve this funny error.

Raul Garcia - MS

As defined in BOL, sp_addolemember cannot be executed in a user-defined transaction (http://msdn2.microsoft.com/en-us/library/ms187750.aspx , see Remarks section); but I am guessing that your customer didn¡¯t initiated an explicit transaction, therefore most likely there is an implicit transaction (http://msdn2.microsoft.com/en-us/library/ms187807.aspx).

Other than making sure that there is no transaction active before you try calling sp_addrolemember, I don¡¯t think there is any workaround.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine