Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 4, 2001

Moving Logins from a SQL 7 Server Previously Upgraded from SQL 6.5

By Andy Warren

I recently had to move several databases from one server to another and ran into a problem with transferring the SQL logins. If you take a look at SQL 7 Books Online for sp_addlogin, it explains how you can use it for transferring logins by setting the optional flag @encryptopt to one of two values; skip_encryption if the password has already been encrypted, or skip_encryption_old if it was encrypted with an older version (with a note to be used for upgrades only).

So based on that, I moved all of the pertinent logins using sp_addlogin with @encryptopt = skip_encryption. No problem so far. Then I detached the databases from the old server, copied to the new server and attached, started testing. Some of our apps worked, some did not because the login failed. I double checked that the encrypted password in syslogins matched on both servers - they did. So what???

It turns out that the sysxlogins table has a column called xstatus which indicates how the password was encrypted. It is set to 2050 for SQL 6.5 logins, to 2 for SQL 7 and SQL 2000 logins. All of the ones that were failing had an xstatus of 2050 on the old server, but an xstatus of 2 on the new server. Eureka!

Whenever someone tried to login, SQL was encrypting the provided password with 7.0 encryption and comparing it to the encrypted password in sysxlogins - which had been encrypted by SQL 6.5. In hindsight this makes sense. In order to upgrade the passwords they would have had to ship code that could decrypt a stored password - not a good idea!

MS has a script posted for moving logins that accounts for the two different xstatus values:

If you're moving logins, you'll want to read this article as well:

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM