I am investigating SQL Server Always On Availability Groups and ran into a problem when setting an application role on the read-only replica database. What really irritates me is the behavior, and I don't know how to interpret the error message.
All I do is call
DECLARE @cookie varbinary(8000);
EXEC sys.sp_setapprole
@rolename = 'TestRole', -- sysname
@password = 'password', -- sysname
@fCreateCookie = 1, -- bit
@cookie = @cookie OUTPUT; -- varbinary(8000)
EXEC sys.sp_unsetapprole @cookie = @cookie; -- varbinary(8000)
which works fine for the first attempt. On the second and all following tries, I receive the following error:
Msg 3961, Level 16, State 1, Procedure sp_setapprole, Line 44 [Batch Start Line 25] Snapshot isolation transaction failed in database 'AGTest' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.
When I execute the same statement on the primary database, I can set the approle on the replica again - once.
I tested different isolation level settings (although I wouldn't be willing to change it for the later productive database), which didn't work. I currently have no further approach to the problem, and google has almost no info for me.
