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.
SOLUTION/WORKAROUND
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_idAnd 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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
4 Comments. Leave new
When I run on my always on I get nothing:
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
In my case issue was for creating new login on the server not user in the database.
how do I change owner for database that are part of always on it says my db is readonly
1. You do it on primary
2. Failover to secondary.
3. Do it on new primary.