November 20, 2008 at 8:45 am
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.
November 20, 2008 at 10:34 am
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
November 20, 2008 at 3:06 pm
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
---------------------------------------------------------------------
November 22, 2008 at 10:51 am
That will work great for me thank you very much for the link.
November 22, 2008 at 1:21 pm
you're welcome
---------------------------------------------------------------------
November 24, 2008 at 8:04 am
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?
November 24, 2008 at 1:01 pm
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?
---------------------------------------------------------------------
November 24, 2008 at 7:51 pm
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
November 25, 2008 at 3:45 pm
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?
November 25, 2008 at 10:30 pm
When the check_policy setting is turned on - yes, the system is going to validate using AD settings for that server.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
November 26, 2008 at 2:37 am
did the script set their default databases correctly? Do you have the exact login failure error as recorded in the SQL errorlog?
---------------------------------------------------------------------
November 26, 2008 at 3:16 pm
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
November 26, 2008 at 4:21 pm
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:
---------------------------------------------------------------------
November 27, 2008 at 12:49 am
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
November 28, 2008 at 9:40 am
-- 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.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply