I have a trigger which requires dropping a member from a role and includes user transactions. However you cannot call sp_droprolemember from within a user transaction. Looking at the code for the procedure gives me a line
%%Owner(Name = @membername).SetRoleMember(RoleID = @roluid, IsMember = 0)
I can find no documentation on SetRoleMember or %%owner and attempts to run this line as an exec statement fails with "syntax error near '%'
Nor can I find any way of dropping a member from a role using T-SQL or DDL constructs.
How do I drop a member from a role using T-SQL code and avoiding sp_droprolemember? And where do I find information about the %%Owner construct?