Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got an Availability Group going and need to map some logins to database users on an active secondary replica. (Node2) Unfortunately, when I try to exec sp_change_users_login or alter user with login, I get an error message telling me the database is read-only. That's no surprise, but I'm not sure how to fix it. I tried failing over last night, fixing the logins on Node2, and then failing back to Node1, but then the login/user mappings just end up wrong on Node1. When I fix those, Node2's logins get out of whack again.

This is a production system, so I can't do anything I want until later tonight. I didn't run into this problem in our test lab. The difference may be that I created the logins before joining the databases before, whereas this time I tried to create them afterwards, and failed to document the process correctly. Taking the AG down and rebuilding it is something I'd rather avoid if I can.

The problem makes sense to me, I just don't really know how to fix it.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

What you will have to do is connect to the instance that has a mis-matched SID, and you'll have to recreate the login and specify an explicit SID. For instance, on the instance where you have the orphaned user and the following returns the user:

exec sp_change_users_login 'report';
go

Copy the SID from the column UserSID. And if you already have an existing login that you want to preserve the name from on that instance, you can't. There is no way to ALTER LOGIN and change the SID. So, you will need to drop the login and recreate it:

drop login YourLogin;
go

create login YourLogin
with
    password = 'password',
    check_policy = off,     -- simple password and no check policy for example only
    sid = 0xC26909...................;
go

Again, you will want to set the sid parameter of CREATE LOGIN to the UserSID you retrieved from executing sp_change_users_login with the report.

Note: You will want to set the password on this/these replica(s) to the same so that you don't have a password mismatch between replicas.

I am assuming that your login(s) in question here are SQL Server logins, as this shouldn't be a problem for Windows Auth domain accounts because the SIDs should be the same on each instance.

You could also leverage contained databases here and have the database principal authenticate at the database level. That way you wouldn't have to worry about orphaned users.

Here is some documentation on this:
BOL reference on Management of Logins and Jobs for the Databases of an Availability Group
KB article on How to transfer logins and passwords between instances of SQL Server

Also, this should go without saying, but test this out in a development/QA environment to verify and ensure proper functionality before hitting production.

share|improve this answer
    
Perfect! I figured there was a way to do this, I just didn't know what to search for. –  Stuart Branham Dec 9 '12 at 0:34
    
Excellent! Glad to help. –  Thomas Stringer Dec 9 '12 at 1:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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