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?

0

1 Answer 1

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.

6
  • We don't have the login on secondary, that is the issue.
    – Allan
    Apr 17, 2019 at 9:38
  • @Allan Can you provide the T-SQL statement you're using to create the login on the primary? Apr 17, 2019 at 19:10
  • I am trying to create using GUI, not by query.
    – Allan
    Apr 18, 2019 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? Apr 18, 2019 at 12:27
  • 1
    @JamesJenkins That's a good guess! Hard to say when they won't share the code or error message :P Apr 18, 2019 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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