Log-in's summary

Question

Log-in's summary

SQL_Easy_btn?

SSC Eights!

Points: 930
More actions
November 20, 2008 at 8:45 am

#203112

I have a change to a production system coming up, where we are moving all the databases from one instance to another.
The question I have is what would be the best way to handle the recreation of the users?
We will be detaching and reattaching the databases to the new instance, but of course the log-in's will not be in the master database on the new instance. My guess is that I will need to use 'sp_change_users_login', after manually creating the log-in on the new instance.
Please tell me there is an easier way.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Script out the logins, with their sids and apply that script on the new server, then you can detach and attach the database without the users been orphaned.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 2

microsoft supply a well known script to do copy logins with the same sids and passwords - sp_help_revlogin

see http://support.microsoft.com/kb/918992

---------------------------------------------------------------------

SQL_Easy_btn? SSC Eights! Points: 930 More actions · Answer 3

That will work great for me thank you very much for the link.

george sibbald SSC Guru Points: 104210 More actions · Answer 4

you're welcome

---------------------------------------------------------------------

SQL_Easy_btn? SSC Eights! Points: 930 More actions · Answer 5

FYI...

First morning into production, none of the user passwords passed through. For the lack of a better word the pw's were corrupt. We had to have all our user passwords reset through the application level.

The script worked perfectly, I had no errors after running it on 'Server A' and no errors after it ran on 'Server B'. So there is no indication that I had executed the script incorrectly.

Has anyone else had the same problem?

george sibbald SSC Guru Points: 104210 More actions · Answer 6

I've not heard of this failing to work before.

was this a 2005 to 2005 transfer?

do the two servers have the same sort order?

what error message did users get when first trying to log in?

---------------------------------------------------------------------

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

SQL Server 2005 now has an option to verify that user passwords pass the policy settings for the system. My guess is that most of the passwords for the users did not pass the policy check and were set to a default value instead.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

SQL_Easy_btn? SSC Eights! Points: 930 More actions · Answer 8

It was a 2005 to 2005 transfer and yes they have the same sort order.

The errors were 'log in failed please see your system administrator', changing their passwords worked and they got in with little to no issues concerning SQL Server.

What is the criteria that they wouldnt have passed? AD password policies?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

When the check_policy setting is turned on - yes, the system is going to validate using AD settings for that server.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

george sibbald SSC Guru Points: 104210 More actions · Answer 10

did the script set their default databases correctly? Do you have the exact login failure error as recorded in the SQL errorlog?

---------------------------------------------------------------------

SQL_Easy_btn? SSC Eights! Points: 930 More actions · Answer 11

The T-SQL that was generated by the sp_help_revlogin all the users had the following code:

CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

and after doing a couple of searches I found that the password policies are only enforced on a per log-in basis, so I am not sure how the passwords got messed up. And yes it set their default databases correctly.

Logon Failure: Reason: Unknown user name or bad password

george sibbald SSC Guru Points: 104210 More actions · Answer 12

thats odd, if check_policy = off that rules out that area as a cause of the logon failure.

I am at a loss to explain why sp_help_revlogin would fail to set up the logins identical to the source server.

sorry.:ermm:

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Can you post one of the CREATE LOGIN statements that the script generated?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Easy_btn? SSC Eights! Points: 930 More actions · Answer 14

-- Login: xxx

CREATE LOGIN [xxx] WITH PASSWORD = xxx HASHED, SID = xxx, DEFAULT_DATABASE = [xxx], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Edited with the 'xxx' for security.