SQL SERVER – Unable to create login AlwaysOn availability group secondary replica

SQL SERVER - Unable to create login AlwaysOn availability group secondary replica erroricons-800x800 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_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)

, ,
Previous Post
SQL SERVER Management Studio – Taking DB Offline Made Easier
Next Post
SQL SERVER – Discovery Report – How to Find Information About Installed Features?

Related Posts

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

    Reply
  • how do I change owner for database that are part of always on it says my db is readonly

    Reply

Leave a Reply

Menu