Adding member on a Role

Hi all,

I'm wondering if I can add a member on my role inside an SP.

something like this.

-- EXEC sp_addrolemember 'RoleName', 'UserName'

but i can't use the above statemnt inside a stored proc. I'm getting this error msg

Msg 15002, Level 16, State 1, Procedure sp_addrolemember, Line 19

The procedure 'sp_addrolemember' cannot be executed within a transaction.

Is there any other alternative way to add a member to a role inside an SP?

Thank you so much and have a nice day to all

My bad it is not inside a [stored proc] it is inside a [TRIGGER after INSERT]|||

You can commit the transaction inside the trigger before you execute sp_addrolemember, just put a COMMIT statement before the call to sp_addrolemember. This could have undesirable effects if there are other triggers, so be careful and test it thoroughly! You should probably make this trigger the last trigger with sp_settriggerorder, which will at least make sure that it never commits before other triggers fire.

The other possibly better option is to create a stored procedure that performs the insert and then calls sp_addrolemember, and make sure that you always use that sproc to insert into the table. Using sprocs exclusively for all data modifications is a good principle anyway.