0

How to drop a user from the secondary database in an AG when it is in read only mode and synchronizing in SQL Server 2012?

The login and user have been deleted from the primary database, and we are trying to recreate the login on primary. But when we are trying to create the login on primary, it is throwing an error saying it already exists.

We suspect due to the user being present on the secondary it is not allowing it to create on the primary. Hence we need a way to create the login by deleting the user in secondary.

Any ideas on how do we do that?

2

You can't do this to the secondary only. Dropping a user modifies the database, which is not possible because the secondary AG database is in read only mode.

You would have to drop it on the primary - then the change will be replicated to the secondary.

From the updated question, it sounds like you're trying to recreate both the login and the user after removing the user from the primary.

It's hard to tell without seeing the actual T-SQL being executed, but you may have better luck with removing the login from the secondary first, as that is a server level object and thus wasn't removed when you dropped the user on the primary.


The login and user have been deleted from the primary database, and we are trying to recreate the login on primary. But when we are trying to create the login on primary, it is throwing an error saying it already exists.

I think it's important to clarify something real quick. A login exists at the instance level, not in any specific database. You can just connect to each instance and create the login.

A user exists at the database level, and is linked to a login. To create the user, you'll need to connect to the primary database (whichever instance that happens to be on), and create the user there. It will automatically be created on the secondary.

  • We don't have the login on secondary, that is the issue. – Allan Apr 17 at 9:38
  • @Allan Can you provide the T-SQL statement you're using to create the login on the primary? – Josh Darnell Apr 17 at 19:10
  • I am trying to create using GUI, not by query. – Allan Apr 18 at 7:35
  • @Allan What's the actual text of the error you're getting when you you try to add the login through the GUI? – Josh Darnell Apr 18 at 12:27
  • 1
    @JamesJenkins That's a good guess! Hard to say when they won't share the code or error message :P – Josh Darnell Apr 18 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.