One of my clients reported a usual behavior of the AlwaysOn availability group. He informed me that he is trying to add a login on the secondary replica in the AlwaysOn AG environment but it is failing with below error:
Msg 3906, Level 16, State 1, Line 3
Failed to update database “SCCMDB” because the database is read-only.
As a normal troubleshooting, I asked to do it from SQL Server Management Studio and got below error.
Create failed for Login ‘TestLogin’. (Microsoft.SqlServer.Smo)
Failed to update database “SCCMDB” because the database is read-only. (Microsoft SQL Server, Error: 3906)
One strange this which he informed me that it works well on primary replica.
I was curious to know why would creating a login would try to write information into a user database? As a part of troubleshooting, I asked to capture a profile trace while reproducing the error. I was able to see that there is a trigger getting fired while creating a login.
We executed this query
SELECT t.name ,t.object_id ,t.is_disabled ,te.type_desc FROM master.sys.server_triggers t INNER JOIN master.sys.server_trigger_events te ON t.object_id = te.object_id
And found that there was a DDL trigger defined for the login creation which was logging an entry in SCCMDB. Once we disabled the trigger, the issue was resolved.
Reference : Pinal Dave (https://blog.sqlauthority.com)