How to get users and logins to match up

I set up a log-shipping stand-by server very carefully (I thought) but at least some of the SQL logins don't work. As I understand it, a database user has an ID that points to the server login. An un-enforced foreign key. So I found out the IDs of the logins on the live server and scripted them as CREATE LOGIN ... WITH SID = Ox etc. Then I restored backups onto the standby server and ran the CREATE LOGIN scripts.

I was feeling pretty smug but today we had to fail over one of the databases and two of the logins got a 'login failed' error. After I ran sp_change_users_login 'AUTO_FIX' with the username and password it was all sorted. But I thought I'd got round this in the way I set the server up.

Where did I go wrong, please?

more ▼

asked Dec 04, 2009 at 11:56 AM in Default

David Wimbush gravatar image

David Wimbush
5.2k 29 31 34

(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest

Hmm. AFAIK correct pwd + correct SID should work. Is it possible to verify that they were correct? And how did you create the CREATE LOGIN scipt? Using the MS solution mentioned here? http://support.microsoft.com/kb/246133

more ▼

answered Jan 11, 2010 at 02:23 PM

Steinar gravatar image

1.7k 3 4 6

You're right Steinar. I screwed it up!
Jul 23, 2010 at 01:49 AM David Wimbush
(comments are locked)
10|1200 characters needed characters left

I suspect that even though you created the SQL Server Logins on the standby server, SQL Server would not have been able to map the Logins to Database Users because log shipping would have not permitted this i.e. the log shipped database would have been either set to read only or placed in a restoring state.

This is why when you bring the log shipped database online, the stored procedure sp_change_users_login 'AUTO_FIX' is able to map the logins to database users successfully.

more ▼

answered Dec 04, 2009 at 02:01 PM

John Sansom gravatar image

John Sansom
897 2

(comments are locked)
10|1200 characters needed characters left

I am not sure I understand here, you mention sp_change_users_login wich should not be needed if you have created the logins with the correct SID's, and that sp does not take pwd as a parameter. But since you also mention password, I wonder if the error simply was that the passwords for your correctly SID-matched logins didnt match in your 2 systems?

more ▼

answered Jan 11, 2010 at 12:11 PM

Steinar gravatar image

1.7k 3 4 6

Hi Steinar, the passwords were definitely the same on both servers. And sp_change_users_login really does have a @Password parameter.
Jan 11, 2010 at 12:39 PM David Wimbush
Arrgh! I should read the BOL first before answering :-) I have used that sp for 12 years, and not noticed.. I learned something today, thanks :-)
Jan 11, 2010 at 01:46 PM Steinar
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 04, 2009 at 11:56 AM

Seen: 2222 times

Last Updated: Dec 04, 2009 at 11:56 AM