How to move databases between computers that are running SQL Server

This step-by-step article describes how to move Microsoft SQL Server user databases and most common SQL Server components between computers that are running SQL Server.

The steps that are described in this article assume that you will not move the master, model, tempdb, or msdb system databases. The steps provide different options for you to transfer logins and the most common components that are contained in the master and msdb databases.

For information about the specific items not transferred when you follow the steps in this article, see the "More information" section.

Note For Microsoft SQL Server 2008, refer to the "Managing Metadata When Making a Database Available on Another Server Instance" topic at the following in SQL Server 2008 Books on Line Web site:

http://msdn.microsoft.com/en-us/library/ms187580.aspx

Note Data migration from SQL Server 2000 to Microsoft SQL Server 2000 (64-bit) is supported. You can attach a 32-bit database to a 64-bit database by using the sp_attach_db system stored procedure or the sp_attach_single_file_db system stored procedure, or by using backup and restore in the 32-bit Enterprise Manager. You can move databases back and forth between the 32-bit and the 64-bit versions of SQL Server. You can also migrate data from SQL Server 7.0 by using the same methods. However, downgrading data to SQL Server 7.0 from SQL Server 2000 (64-bit) is not supported. A description of each method follows.

Back to the top

Step 1: How to move user databases

If you are using SQL Server 2005, you can use the same method to migrate data from SQL Server 7.0 or from SQL Server 2000. However, the management tool in Microsoft SQL Server 2005 differs from the management tool in SQL Server 7.0 or in SQL Server 2000. You should use SQL Server Management Studio instead of the SQL Server Enterprise Manager. Additionally, you should use the SQL Server Import and Export Wizard (DTSWizard.exe) instead of the Data Transformation Services Import and Export Data Wizard.

To move user databases, use one of the following three methods.

Method 1: Backup and restore the user databases

Back up the user databases on the source server, and then restore the user databases to the destination server.

· The database can be used when the backup is in process. If users perform INSERT, UPDATE, or DELETE statements on the database after the backup is complete, the backup will not contain these changes. If you must transfer all changes, you can transfer the changes with minimal downtime if you perform both a transaction log backup and a full database backup.

1. Restore the full database backup on the destination server and specify the WITH NORECOVERY option.

Note To prevent additional database modifications, direct users to quit database activity on the source server.

2. Perform a transaction log backup and restore the transaction log backup to the destination server by using the WITH RECOVERY option. Downtime is limited to the time of the transaction log backup and restore. For more information, see the "RESTORE" sub-topic in the "Transact-SQL Reference" topic of SQL Server Books Online.

· The database on the destination server will be the same size as the database on the source server. To reduce the size of the database, you must either reduce the source database before you perform the backup, or reduce the destination database after the restore is completed. For more information, see the "Shrinking a Database" sub-topic in the "Creating and Maintaining Databases" heading of SQL Server Books Online.

· If you restore the database to a different file location than the source database, you must specify the WITH MOVE option. For example, on the source server the database is in the D:\Mssql\Data folder. The destination server does not have a D drive, and you want to restore the database to the C:\Mssql\Data folder. For more information about how to restore a database to a different location, click the following article numbers to view the articles in the Microsoft Knowledge Base:

221465 Using the WITH MOVE option with the RESTORE statement

304692 Moving SQL Server 7.0 databases to a new location with BACKUP and RESTORE

· If you want to overwrite a preexisting database on the destination server, you must specify the WITH REPLACE option. For more information, see the "RESTORE" sub-topic in the "Transact-SQL Reference" topic of SQL Server Books Online.

· Depending on the version of SQL Server to which you restore, the character set, sort order, and Unicode collation may have to be the same on both the source and destination servers. For more information, see the "Note about collation" later in this section.

Method 2: Use the "sp_detach_db" and "sp_attach_db" stored procedures

To use the sp_detach_db and sp_attach_db stored procedures, follow these steps:

1. Detach the database on the source server by using the sp_detach_db stored procedure. You must copy the .mdf, .ndf and .ldf files associated with the database to the destination server. See this table for a description of the file types:

File name extension	Description
.mdf	Primary data file
.ndf	Secondary data file
.ldf	Transaction log file

2. Attach the database on the destination server by using the sp_attach_db stored procedure and point to the files you copied to the destination server in the previous step. For more information about how to use these methods, click the following article number to view the article in the Microsoft Knowledge Base:

224071 How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

· The database is inaccessible after the detach, and you cannot use the database when you copy the files. All data that is contained in the database at the point in time of the detach is moved.

· The character set, sort order, and Unicode collation may have to be the same on both servers when you use the Attach or Detach method. For more information, see the following note about collation.

Note about collation If you move databases between SQL Server 7.0 servers by using the backup and restore or Attach and Detach methods, the character set, sort order and Unicode collation must be the same on both servers. If you move databases from SQL Server 7.0 to SQL Server 2000 or between SQL Server 2000 servers, the database maintains the collation of the source database. This means that if the destination server that is running SQL Server 2000 has a different collation than the source database, the destination database has a different collation than the destination server's master, model, tempdb, and msdb databases. For more information, see the "Mixed Collation Environments" topic in SQL Server 2000 Books Online.

Method 3: Use the Import and Export Data Wizard to copy objects and data between SQL Server databases

You can copy a whole database or selectively copy objects and data from the source database to the destination database by using the Data Transformation Services Import and Export Data Wizard.

· The source database may be used during the transfer. If the source database is used during the transfer, you may see some blocking when the transfer is in progress.

· When you use the Import and Export Data Wizard, the character set, sort order and collation does not have to be the same between the source server and destination server.

· Because unused space in the source database does not move, the destination database may not have to be as large as the source database. Similarly, if you move only some objects, the destination database may not have to be as large as the source database.

· SQL Server 7.0 Data Transformation Services may not transfer text and image data longer than 64 KB correctly. This problem does not apply to the SQL Server 2000 version of Data Transformation Services. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

257425 FIX: DTS Object Transfer does not transfer BLOB data greater than 64 KB

Back to the top

Step 2: How to transfer logins and passwords

If you do not transfer the logins from the source server to the destination server, your current SQL Server users may be unable to log on to the destination server. You can transfer the logins and passwords by using the instructions in the following Microsoft Knowledge Base article:

246133 How to transfer logins and passwords between instances of SQL Server

The default databases for the logins on the destination server may be different from the default database for the logins on the source server. You can change the default database for a logon with the sp_defaultdb stored procedure. For more information, see the "sp_defaultdb" sub-topic of the "Transact-SQL Reference" topic in SQL Server Books Online.

Back to the top

Step 3: How to resolve orphaned users

After you transfer logins and passwords to the destination server, users may be unable to access the database. Logins are associated to users by the security identifier (SID), and if the SID is inconsistent after you move a database, SQL Server may deny the user access to the database. This problem is known as an orphaned user. If you transfer logins and passwords by using the SQL Server 2000 DTS Transfer Login feature, you will probably have orphaned users. Additionally, integrated logins granted access on a destination server in a different domain than the source server cause orphaned users.

1. Look for orphaned users. Open Query Analyzer on the destination server, and then run the following code in the user database that you moved:

exec sp_change_users_login 'Report'

The procedure lists any orphaned users who do not link to a logon. If no users are listed, skip step 2 and step 3 and go to step 4.

2. Resolve the orphaned users. If a user is orphaned, database users can log on to the server successfully but will not have permission to access the database. If you try to grant the logon access to the database, you receive the following error message because the user already exists:

Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.

For more information about how to resolve orphaned users, click the following article numbers to view the articles in the Microsoft Knowledge Base:

240872 How to resolve permission issues when you move a database between servers that are running SQL Server

This article contains instructions about how to map the logins to the database users and resolves users orphaned from standard SQL Server logins and integrated logins.

274188 "Troubleshooting Orphaned Users" topic in Books Online is incomplete

This article describes how to use the sp_change_users_login stored procedure to correct the orphaned users one by one. The sp_change_users_login stored procedure only resolves users orphaned from standard SQL Server logins.

3. If the database owner (dbo) is listed as orphaned, run this code in the user database:

exec sp_changedbowner 'sa'

The stored procedure changes the database owner to dbo and corrects the issue. To change the database owner to another user, run sp_changedbowner again with the user you want. For more information, see the "sp_changedbowner" sub-topic in the "Transact-SQL Reference" topic of SQL Server Books Online.

4. If your destination server is running SQL Server 2000 Service Pack 1, the database owner user may not be in the list in the Users folder in Enterprise Manager after you perform the attach or restore or both. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

305711 BUG: DBO user does not display in Enterprise Manager

5. You may receive the following error message if you try to change the system administrator (sa) password through Enterprise Manager if the logon that was mapped to dbo on the source server does not exist on the destination server:

Error 21776: [SQL-DMO] The name 'dbo' was not found in the Users collection. If the name is a qualified name, use [] to separate various parts of the name, and try again.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

218172 Cannot change SA password in Enterprise Manager

Warning If you restore or attach the database again, the database users may be re-orphaned and you have to repeat step 3.

Back to the top

Step 4: How to move jobs, alerts, and operators

Step 4 is optional. You can generate scripts for all jobs, alerts and operators on the source server, and then run the script on the destination server.

· To move jobs, alerts and operators, follow these steps:

1. Open the SQL Server Enterprise Manager, and then expand the Management folder.

2. Expand SQL Server Agent, and then either right-click Alerts, Jobs, or Operators.

3. Click All Tasks, and then click Generate SQL Script. For SQL Server 7.0, click Script All Jobs, or Alerts, or Operators.

You will have the option to generate scripts for All Alerts, All Jobs or All Operators based on the item you right-click.

· You can move jobs, alerts and operators from SQL Server 7.0 to SQL Server 2000 or between computer servers that are running SQL Server 7.0 and SQL Server 2000.

· If you have operators that are set up for notification by SQLMail on the source server, you have to set up SQLMail on the destination server to have the same functionality. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

263556 How to configure SQL Mail

Back to the top

Step 5: How to move DTS packages

Step 5 is optional. If DTS packages are stored on the source server in the SQL Server or the repository, you can move them if you want. To move DTS packages between servers, use one of the following methods.

Method 1

1. Save the DTS package on the source server to a file, and then open the DTS package file on the destination server.

2. Save the package on the destination server to the SQL Server, or to the repository.
Note You have to move each package one by one in separate files.

Method 2

1. Open each DTS package in the DTS Designer.

2. On the Package menu, click Save As.

3. Specify the destination SQL Server.

Note The package may not run correctly on the new server. You may have to change the package, and change any references in the package to connections, files, data sources, profiles and other information that is located on the old source server, to reference the new destination server. You must make these changes on a package by package basis based on the design of each package.

Back to the top

Step 6: Change the sp_configure settings to match the previous system

You may have to change the settings so that they match the settings in the new system. For example, if the new system has more memory or if it is running different SQL instances and applications, you may want to change the min and max server memory settings or the AWE setting. You may have to change the MAXDOP setting if the number of CPU cores that are exposed to the operating system have changed.

Back to the top

MORE INFORMATION

You may also want to move other items such as linked servers, mirroring, replication, log shipping, full-text catalogs, named backup devices, maintenance plans, database diagrams, database snapshots, credentials and proxy accounts, endpoints, server scoped DDL triggers (such as a logon trigger), or other items involving either master or msdb. Examine the source server for these configurations and take steps to set them up manually on the destination server, if you want.

For more information about how to move full text components, click the following article number to view the article in the Microsoft Knowledge Base:

240867 How to move, copy, and back up full-text catalog folders and files

Database diagrams and backup and restore history are not moved if you follow the steps in this article. If you must move this information, move the msdb system database. For information about how to move the msdb database, see the Microsoft Knowledge Base articles that are referenced in the "Step 1: How to move user databases" section. If you move the msdb database, you do not have to follow "Step 4: How to move jobs, alerts, and operators" or "Step 5: How to move DTS packages."

Back to the top

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

320125 How to move a database diagram

How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008

http://support.microsoft.com/kb/918992/en-us

View products that this article applies to.

On This Page

· INTRODUCTION

· MORE INFORMATION

o Remarks

· REFERENCES

Expand all | Collapse all

INTRODUCTION

This article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005, and Microsoft SQL Server 2008, on different servers.

For more information about how to transfer the logins and the passwords between instances of other versions of SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

246133 How to transfer logins and passwords between instances of SQL Server

Back to the top

MORE INFORMATION

In this article, server A and server B are different servers. Additionally, both server A and server B are running SQL Server 2005.

Note This information also applies to SQL Server 2008.

After you move a database from the instance of SQL Server on server A to the instance of SQL Server on server B, the users may not be able to log in to the database on server B. Additionally, the users may receive the following error message:

This problem occurs because you did not transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B.

To transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B, follow these steps:

1. On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database.

2. Open a new Query Editor window, and then run the following script.

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

USE master

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

DROP PROCEDURE sp_hexadecimal

CREATE PROCEDURE sp_hexadecimal

@binvalue varbinary(256),

@hexvalue varchar (514) OUTPUT

DECLARE @charvalue varchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

DECLARE @tempint int

DECLARE @firstint int

DECLARE @secondint int

SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

SELECT @firstint = FLOOR(@tempint/16)

SELECT @secondint = @tempint - (@firstint*16)

SELECT @charvalue = @charvalue +

SUBSTRING(@hexstring, @firstint+1, 1) +

SUBSTRING(@hexstring, @secondint+1, 1)

SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

DROP PROCEDURE sp_help_revlogin

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @type varchar (1)

DECLARE @hasaccess int

DECLARE @denylogin int

DECLARE @is_disabled int

DECLARE @PWD_varbinary varbinary (256)

DECLARE @PWD_string varchar (514)

DECLARE @SID_varbinary varbinary (85)

DECLARE @SID_string varchar (514)

DECLARE @tmpstr varchar (1024)

DECLARE @is_policy_checked varchar (3)

DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)

DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

ELSE

DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

IF (@type IN ( 'G', 'U'))

BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'

END

ELSE BEGIN -- SQL Server authentication

-- obtain password and sid

SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state

SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )

BEGIN

SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

END

IF ( @is_expiration_checked IS NOT NULL )

BEGIN

SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

END

IF (@denylogin = 1)

BEGIN -- login is denied access

SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

END

ELSE IF (@hasaccess = 0)

BEGIN -- login exists but does not have access

SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

END

IF (@is_disabled = 1)

BEGIN -- login is disabled

SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

END

PRINT @tmpstr

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

Note This script creates two stored procedures in the master database. The two stored procedures are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure.

3. Run the following statement.

EXEC sp_help_revlogin

The output script that is generated by the sp_help_revlogin stored procedure is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

4. On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

Important Before you go to step 5, review the information in the "Remarks" section.

5. Open a new Query Editor window, and then run the output script that is generated in step 3.

Back to the top

Remarks

Review the following information before you run the output script on the instance on server B:

· Review the output script carefully. If server A and server B are in different domains, you have to modify the output script. Then, you have to replace the original domain name with the new domain name in the CREATE LOGIN statements. The integrated logins that are granted access in the new domain do not have the same SID as the logins in the original domain. Therefore, the users are orphaned from these logins. For more information about how to resolve these orphaned users, click the following article number to view the article in the Microsoft Knowledge Base:

240872 How to resolve permission issues when you move a database between servers that are running SQL Server

If server A and server B are in the same domain, the same SID is used. Therefore, the users are not likely to be orphaned.

· In the output script, the logins are created by using the encrypted password. This is because of the HASHED argument in the CREATE LOGIN statement. This argument specifies that the password that is entered after the PASSWORD argument is already hashed.

· By default, only a member of the sysadmin fixed server role can run a SELECT statement from the sys.server_principals view. Unless a member of the sysadmin fixed server role grants the necessary permissions to the users, the users cannot create or run the output script.

· The steps in this article do not transfer the default database information for a particular login. This is because the default database may not always exist on server B. To define the default database for a login, use the ALTER LOGIN statement by passing in the login name and the default database as arguments.

· The sort order of server A may be case insensitive, and the sort order of server B may be case sensitive. In this case, the users must type all the letters in the passwords as uppercase letters after you transfer the logins and the passwords to the instance on server B.

Alternatively, the sort order of server A may be case sensitive, and the sort order of server B may be case insensitive. In this case, the users cannot log in by using the logins and the passwords that you transfer to the instance on server B unless one of the following conditions is true:

o The original passwords contain no letters.

o All the letters in the original passwords are uppercase letters.

The sort order of both server A and server B may be case sensitive, or the sort order of both server A and server B may be case insensitive. In these cases, the users do not experience a problem.

· A login that already is in the instance on server B may have a name that is the same as a name in the output script. In this case, you receive the following error message when you run the output script on the instance on server B:

Msg 15025, Level 16, State 1, Line 1
The server principal 'MyLogin' already exists.

Similarly, a login that already is in the instance on server B may have a SID that is the same as a SID in the output script. In this case, you receive the following error message when you run the output script on the instance on server B:

Msg 15433, Level 16, State 1, Line 1
Supplied parameter sid is in use.

Therefore, you must do the following:

1. Review the output script carefully.

2. Examine the contents of the sys.server_principals view in the instance on server B.

3. Address these error messages accordingly.

· In SQL Server 2005, the SID for a login is used as the basis for implementing database-level access. A login may have two different SIDs in two different databases on a server. In this case, the login can only access the database that has the SID that matches the SID in the sys.server_principals view. This problem may occur if the two databases are consolidated from two different servers. To resolve this problem, manually remove the login from the database that has a SID mismatch by using the DROP USER statement. Then, add the login again by using the CREATE USER statement.

Back to the top

REFERENCES

For more information about how to troubleshoot orphaned users, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms175475.aspx

For more information about the CREATE LOGIN statement, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/ms189751.aspx

For more information about the ALTER LOGIN statement, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/ms189828.aspx

How to resolve permission issues when you move a database between servers that are running SQL Server

http://support.microsoft.com/kb/240872/en-us

View products that this article applies to.

This article was previously published under Q240872

On This Page

· SUMMARY

· MORE INFORMATION

o Restrictions

o Map the standard and integrated logins

o SQL Server 7.0 and SQL Server 2000

o SQL Server 2005

· REFERENCES

Expand all | Collapse all

SUMMARY

This article describes how to map the standard and integrated logins in order to resolve permission issues when you move a database between servers that are running SQL Server.

Back to the top

MORE INFORMATION

When you move a database from one server that is running SQL Server to another server that is running SQL Server, a mismatch may occur between the security identification numbers (SIDs) of the logins in the master database and the users in the user database. By default, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 provide the sp_change_users_login system stored procedure to map these mismatched users. However, you can only use the sp_change_users_login stored procedure to map standard SQL Server logins and you must perform these mapping for one user at a time. For more information about the sp_change_users_login stored procedure, see the "sp_change_users_login" topic in SQL Server 7.0,SQL Server 2000, and SQL Server 2005 Books Online.

In SQL Server 7.0 or later versions, you can maintain the mapping between the logins in the master database and the users in the user database by using the SIDs. This mapping is required to maintain correct permissions for the logins in the user databases. When this mapping is lost, the logins have permission issues that include but are not limited to the following:

· If the SQL Server login does not exist on the new server, and the user tries to log on, the user may receive the following error message:

Server: Msg 18456, Level 16, State 1
Login failed for user '%ls'.

· If the SQL Server login exists on the new server, but the SID in the master database differs from the SID in the user database, the user can log on to SQL Server successfully; however, when the user tries to access that database, the user may receive the following error message:

Server: Msg 916, Level 14, State 1, Line1
Server user '%.*ls' is not a valid user in database '%.*ls'.

Note In SQL Server 2005, the user may receive the following error message:

Server user '%s' is not a valid user in database '%s'. Add the user account into the database first.

For more information about the SQL Server 7.0 Security model, see the "Microsoft SQL Server 7.0 Security" white paper. To view the white paper, visit the following Microsoft Web site:

http://msdn2.microsoft.com/en-us/library/Aa226173(SQL.70).aspx

For more information about the SQL Server 2000 Security model, click the following article number to view the article in the Microsoft Knowledge Base:

322712 Microsoft SQL Server 2000 S322712 Security Features and Best Practices

Back to the top

Restrictions

· If there are users in the sysusers table without a prefix of the computer name or the domain name that own objects, and these objects are referenced in applications by using the two-part name username.objectname, the application may break because the sp_sidmap stored procedure renames these users with the prefix of the computer name or domain name as it appears in the sysxlogins table. To work around this problem, after the sp_sidmap stored procedure is completed, rename the users who were affected in the sysusers table to their former names or contact your primary support provider.

· This article does not consider aliases. You must manage the aliases manually.

· If a standard SQL Server login does not exist on the new SQL Server server, you can add the login with a NULL password. You may have to change the password for these logins accordingly.

· If a user was created in the user database with a name that differs from that which appears in the sysxlogins table, it is impossible to know the corresponding login for that user. Therefore, before you run the sp_sidmap stored procedure:

1. Transfer all the objects that this user owns to a staging database.

2. Drop the user, add the user that has the correct name, and then transfer back all the objects for this user.

· If a user has neither a corresponding login nor a prefix of either the local computer name or the domain name, you receive a message for this user. This message indicates that you must first add the user at the Windows level and then add it to the SQL Server as a login. After you do this, you must run the sp_sidmap stored procedure again.

· If a user has a prefix of either the domain name or the local Windows server name, but the corresponding login does not exist in the sysxlogins table, the stored procedure tries to add this as a new login to SQL Server. If the Windows user does not exist, it generates an output message in the results window and then manually creates the login after it first adds the Windows user.

· If there is more than one login for a user in the sysusers table, you see an output message in the results file and it lists all the logins that have the same username. At this point, you must manually intervene to make sure that the user corresponds to only one login.

Example If the sysusers table has a user named "johndoe" and the sysxlogins table has logins with names such as "Test\johndoe" and "Test2\johndoe", when you run the stored procedure, you receive a message that states that one of the users has more than one login and that the System Administrator must choose one. This is the only time that you must run the second stored procedure, sp_prefix_sysusersname, which is provided in this article. Additionally, this situation is described in detail in the Readme.txt file.

Back to the top

Map the standard and integrated logins

After you move a database from one server that is running SQL Server server to another server that is running SQL Server server, follow these steps for minimal user intervention:

Back to the top

SQL Server 7.0 and SQL Server 2000

1. Make sure that there is a login in the sysxlogins table in the master database for each user in the sysusers table of the database.

Note To add a standard SQL Server login, see the "sp_addlogin" topic in SQL Server Books Online. To add an integrated SQL Server login, see the "sp_grantlogin" topic in SQL Server Books Online.

2. Download the MapSids.exe file, and then extract the Sp_sidmap.sql and Readme.txt files.

3. Log on to the server that is running SQL Server as a system administrator, and then run the Sp_sidmap.sql file in the user database. Running the Sp_sidmap.sql file creates the two stored procedures, sp_sidmap and sp_prefix_sysusersname.

4. Make sure that the database is not accessed by any other user than the one who is running the stored procedures.

5. Make sure that Query Analyzer displays results in text format and not in grid format. To do this, either press the CTRL^T keys, or click Query, and then click Results in Text. This is very important so that you can view the results and the informational messages in one window and save the output to a text file. You might need this file later to resolve some of the mappings.

6. Because you cannot verify whether the parameters are passed correctly, make sure to pass them correctly to the sp_sidmap stored procedure:

EXEC sp_SidMap @old_domain = old_domain_name,

@new_domain = new_domain_name,

@old_server = old_server_name,

@new_server = new_server_name

Replace the values for the old and new domain names and server names appropriately.

7. Save the results in a file and follow the directions that are provided in the Readme.txt file.

Note When you run these stored procedures, the sysusers table is the only table that changes in the database. To return to a state where you started, restore the database from the backup or reattach the database.

Back to the top

SQL Server 2005

If you are running SQL Server 2005, use the WITH LOGIN clause of the ALTER USER statement to remap a user to a new login. For more information, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn.microsoft.com/en-us/library/ms176060.aspx

Note To use the WITH LOGIN clause of the ALTER USER statement, you must apply SQL Server 2005 Service Pack 2.

Back to the top

REFERENCES

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

274188 "Troubleshooting orphaned users" topic in Books Online is incomplete

246133 How to transfer logins and passwords between instances of SQL Server

168001 User logon and/or permission errors after restoring dump

298897 SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When Database Is Moved

How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

http://support.microsoft.com/kb/224071/en-us

View products that this article applies to.

This article was previously published under Q224071

On This Page

· SUMMARY

· MORE INFORMATION

o Prerequisites

o Moving user databases

o Moving sample databases

o Moving the model database

§ SQL Server 7.0

§ SQL Server 2005 and SQL Server 2000

o Moving the MSDB database

§ SQL Server 7.0

§ SQL Server 2005 and SQL Server 2000

o Moving the master database

§ SQL Server 7.0 and SQL Server 2000

§ SQL Server 2005

o Moving the tempdb database

· REFERENCES

Expand all | Collapse all

SUMMARY

This article describes how to change the location of the data files and of the log files for any Microsoft SQL Server 2005, SQL Server 2000, or SQL Server 7.0 database.

For more information about how to move system databases in SQL Server 2008, see the "Moving System Databases" topic in SQL Server Books Online. To view this topic, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms345408.aspx

Back to the top

MORE INFORMATION

The steps that you must follow to change the location for some SQL Server system databases differ from the steps that you must follow to change the location for user databases. These special cases are described separately.

Note SQL Server 7.0 system databases are not compatible with SQL Server 2000. Do not attach SQL Server 7.0 master, model, msdb or distribution databases to SQL Server 2000. If you are using SQL Server 2005, you can only attach databases of SQL Server 2005 to an instance. All the examples in this article assume that SQL Server is installed in the D:\Mssql7 folder. Additionally, the examples assume that all data files and log files are located in the default D:\Mssql7\Data folder. The examples move the data files and the log files for all the databases to the E:\Sqldata folder.

The default data location for SQL 2000 and 2005 editions are as follows:

· For SQL Server 2000 32 bit editions, visit the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/aa176560(SQL.80).aspx

· For SQL Server 2000 64 bit editions, visit the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/aa274567(SQL.80).aspx

· For SQL Server 2005 editions, visit the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/ms143547(SQL.90).aspx

Back to the top

Prerequisites

· Make a current backup of all databases, especially the master database, from their current location.

· You must have system administrator (sa) permissions.

· You must know the name and the current location of all data files and log files for the database.

Note You can determine the name and the current location of all files that a database uses by using the sp_helpfile stored procedure:

use <database_name>

sp_helpfile

· You should have exclusive access to the database that you are moving. If you experience problems during the process, and if you cannot access a database that you have moved or if you cannot start SQL Server, examine the SQL Server error log and SQL Server Books Online for more information about the errors that you are experiencing.

Back to the top

Moving user databases

The following example moves a database that is named mydb. This database contains one data file, Mydb.mdf, and one log file, Mydblog.ldf. If the database that you are moving has more data files or log files, specify the files in a comma-delimited list in the sp_attach_db stored procedure. The sp_detach_db procedure does not change regardless of how many files the database contains because the sp_detach_db procedure does not list the files.

1. Detach the database as follows:

use master

sp_detach_db 'mydb'

2. Next, copy the data files and the log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).

3. Re-attach the database. Point to the files in the new location as follows:

use master

sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'

Verify the change in file locations by using the sp_helpfile stored procedure:

use mydb

sp_helpfile

The filename column values should reflect the new locations.

Note Microsoft Knowledge Base article 922804 describes an issue for SQL Server 2005 databases on a network-attached storage. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

922804 FIX: After you detach a Microsoft SQL Server 2005 database that resides on network-attached storage, you cannot reattach the SQL Server database

Consider this issue. Additionally, consider the permissions that are applied to a database when it is detached in SQL Server 2005. For more information, see the "Detaching and Attaching a Database" section of the "Securing Data and Log Files" topic in SQL Server Books Online. To view this topic, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms189128.aspx

Back to the top

Moving sample databases

To move the pubs sample database and the Northwind sample database in SQL Server 2000 or in SQL Server 7.0, or to move the AdventureWorks sample database and the AdventureWorksDW sample database in SQL Server 2005, follow the same procedure for moving user databases.

Back to the top

Moving the model database

SQL Server 7.0

1. Make sure that the SQL Server Agent is not currently running.

2. Follow the same procedure for moving user databases.

SQL Server 2005 and SQL Server 2000

In SQL Server 2005 and in SQL Server 2000, you cannot detach system databases by using the sp_detach_db stored procedure. When you try to run the sp_detach_db 'model' statement, you receive the following error message:

Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.

To move the model database, you must start SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database.

Note You will not be able to access any user databases after you do this. You must not perform any operations, other than the following steps, while you use this trace flag. To add trace flag 3608 as a SQL Server startup parameter on SQL Server 2000, follow these steps:

1. In SQL Server Enterprise Manager, right-click the server name, and then click Properties.

2. On the General tab, click Startup Parameters.

3. Add the following new parameter:

-c -m -T3608

If you are using SQL Server 2005, you can use SQL Server Configuration Manager to change the startup parameters of the SQL Server service. For more information about how to change the startup parameters, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms190737.aspx

After you add the -c option, the -m option, and trace flag 3608, follow these steps:

1. Stop and then restart SQL Server.

2. Detach the model database by using the following commands:

use master

sp_detach_db 'model'

3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder to the E:\Sqldata folder.

4. Reattach the model database by using the following commands:

use master

sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'

5. Remove -c -m -T3608 from the startup parameters in SQL Server Enterprise Manager or in SQL Server Configuration Manager.

6. Stop and then restart SQL Server. You can verify the change in file locations by using the sp_helpfile stored procedure. For example, use the following command:

use model

sp_helpfile

Back to the top

Moving the MSDB database

SQL Server 7.0

Note If you are using this procedure while moving the msdb and model databases, you must reattach the model database first, and then reattach the msdb database. Follow these steps:

1. Make sure that the SQL Server Agent is not currently running.

2. Follow the same procedure for moving user databases.

Note If SQL Server Agent is running, the sp_detach_db stored procedure will not succeed and you will receive the following message:

Server: Msg 3702, Level 16, State 1, Line 0
Cannot drop the database 'msdb' because it is currently in use.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server 2005 and SQL Server 2000

To move the MSDB database, you must start SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database. To add the -c option, the -m option, and trace flag 3608, follow the steps in the "Moving the model database" section. After you add the -c option, the -m option and trace flag 3608, follow these steps:

1. Stop, and then restart SQL Server.

2. Make sure that the SQL Server Agent service is not currently running.

3. Detach the msdb database as follows:

use master

sp_detach_db 'msdb'

4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).

5. Remove -c -m -T3608 from the startup parameters box in Enterprise Manager.

6. Stop and then restart SQL Server.

Note If you try to reattach the msdb database by starting SQL Server together with the -c option, the -m option, and trace flag 3608, you may receive the following error message:

Server: Msg 615, Level 21, State 1, Line 1
Could not find database table ID 3, name 'model'.

7. Reattach the msdb database as follows:

use master

sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf'

Note If you use this procedure together with moving the model database, you are trying to detach the msdb database while you detach the model database. When you do this, you must reattach the model database first, and then reattach the msdb database. If you reattach the msdb database first, you receive the following error message when you try to reattach the model database:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

In this case, you must detach the msdb database, reattach the model database, and then reattach the msdb database,

After you move the msdb database, you may receive the following error message:

Error 229: EXECUTE permission denied on object 'ObjectName', database 'master', owner 'dbo'.

This problem occurs because the ownership chain has been broken. The database owners for the msdb database and for the master database are not the same. In this case, the ownership of the msdb database had been changed. To work around this problem, run the following Transact-SQL statements. You can do this by using the Osql.exe command-line utility (SQL Server 7.0 and SQL Server 2000) or the Sqlcmd.exe command-line utility (SQL Server 2005):

USE MSDB

EXEC sp_changedbowner 'sa'

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

272424 Object ownership chain checking across databases depends on the login that is mapped to the object owners

Back to the top

Moving the master database

SQL Server 7.0 and SQL Server 2000

1. Change the path for the master data files and the master log files in SQL Server Enterprise Manager.

Note You may also change the location of the error log here.

2. Right-click the SQL Server in Enterprise Manager and then click Properties.

3. Click Startup Parameters to see the following entries:

-dD:\MSSQL7\data\master.mdf

-eD:\MSSQL7\log\ErrorLog

-lD:\MSSQL7\data\mastlog.ldf

-d is the fully qualified path for the master database data file.

-e is the fully qualified path for the error log file.

-l is the fully qualified path for the master database log file.

4. Change these values as follows:

a. Remove the current entries for the Master.mdf and Mastlog.ldf files.

b. Add new entries specifying the new location:

-dE:\SQLDATA\master.mdf

-lE:\SQLDATA\mastlog.ldf

5. Stop SQL Server.

6. Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).

7. Restart SQL Server.

SQL Server 2005

For more information about moving the master database and the Resource database, visit the following MSDN Web site: http://msdn2.microsoft.com/en-us/library/ms345408.aspxYou may experience a failure when you move the master database and the Resource database. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

918695 You may experience a failure when you install SQL Server 2005 Service Pack 1 on an instance of SQL Server 2005

Back to the top

Moving the tempdb database

You can move tempdb files by using the ALTER DATABASE statement.

1. Determine the logical file names for the tempdb database by using sp_helpfile as follows:

use tempdb

sp_helpfile

The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.

Use the ALTER DATABASE statement, specifying the logical file name as follows:

use master

Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')

Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')

You should receive the following messages that confirm the change:

Message 1

File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.

Message 2

File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.

2. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.

3. Stop and then restart SQL Server.

Back to the top

REFERENCES

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

912397 The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid

274188 "Troubleshooting orphaned users" topic in Books Online is incomplete

246133 How to transfer logins and passwords between instances of SQL Server

168001 User logons and permissions on a database may be incorrect after the database is restored

For more information, see the following books:

Microsoft Corporation
Microsoft SQL Server 7.0 System Administration Training Kit
Microsoft Press, 2001

Microsoft Corporation
MCSE Training Kit: Microsoft SQL Server 2000 System Administration
Microsoft Press, 2001

Microsoft Corporation
Microsoft SQL Server 2000 Resource Kit
Microsoft Press, 2001

Back to the top

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Usefor other considerations.

How to transfer a database from one collation to another collation in SQL Server

http://support.microsoft.com/kb/325335/en-us

View products that this article applies to.

This article was previously published under Q325335

On This Page

· SUMMARY

o What is collation?

o When to use the Use Collation option in DTS

o Transfer methods that do not change a database's collation

o Transfer a database from one collation in SQL Server 7.0 to a different collation in SQL Server 7.0

o Transfer a database from one collation in SQL Server 7.0 to a collation in SQL Server 2000

o Transfer a database from one collation in SQL Server 2000 to a different collation in SQL Server 2000

o Change the collation of the master database

§ Change the collation of the master database in SQL Server 2000

§ Change the collation of the master database in SQL Server 7.0

· REFERENCES

Expand all | Collapse all

SUMMARY

This article defines collation and describes how to transfer a database from one collation in Microsoft SQL Server to another collation in SQL Server. The same concepts and discussions about SQL Server 2000 also apply to SQL Server 2005.

Back to the top

What is collation?

A collation specifies the bit patterns that represent each character. It also specifies the rules that are used to sort and to compare the characters. A collation has the following characteristics:

· Language

· Case sensitivity

· Accent sensitivity

· Kana sensitivity

To know the collation that a server is currently using, you can run the sp_helpsort system procedure in SQL Query Analyzer.

SQL Server 7.0 does not support databases that have multiple collations. Therefore, all the databases that you create in SQL Server 7.0 use the default collation. SQL Server 2000 supports multiple collations. SQL Server 2000 databases can have collations other than the default collation. Additionally, SQL Server 2000 also supports columns that have collations other than the collations of the databases where they were created.

Back to the top

When to use the Use Collation option in DTS

SQL Server 2000 can have multiple databases or columns that have collations other than the default collation. Because of this, a new option that is named Use Collation is introduced in Data Transformation Services (DTS). The behavior of the Use Collation option is determined by the type of transfer that you are performing. If you transfer data between two instances of SQL Server 2000 and you enable the Use Collation option, the data is translated from the source code page to the destination code page. If you do not enable the Use Collation option and the code pages are the same on both instances of SQL Server 2000, a direct data transfer occurs. If the code pages are different, the data from the source code page is translated to the destination code page. However, the translation may not be correct when you transfer the data.

Note The collation is important if the collation is used for the data itself and if a column uses the COLLATE clause. The Use Collation option determines whether a code page translation occurs when the data is transferred from one collation to another collation. The Use Collation option does not affect whether the COLLATE property of a column definition is set. Therefore, if a source table contains a column that was created with a specific collation by using the COLLATE clause, that collation persists when data is transferred, regardless of whether the Use Collate option is enabled in the Data Transformation Services Wizard.

Back to the top

Transfer methods that do not change a database's collation

The following methods do not change a database's collation:

· Backup and restore: Restoring a database on a server that has a different collation than the server that is used for the backup does not convert the restored database to the new collation. The database collation remains as is.

· Detach and reattach: If you detach a database that was created with one collation and you reattach the database to another server that has a different collation, the collation of the database does not change. The collation of the database remains as is.

· Copy Database Wizard: The Copy Database Wizard essentially automates the process of detaching and reattaching. The collation of the database remains as is.

Note The Copy Database Wizard is available in SQL Server 2000. However, the Copy Database Wizard is not available in SQL Server 7.0.

Back to the top

Transfer a database from one collation in SQL Server 7.0 to a different collation in SQL Server 7.0

To change the collation of a database between two computers that are running SQL Server 7.0, you must create the user database and all the objects on the destination server and then transfer the data by using DTS or the bcp utility.

To transfer a database from a computer that is running SQL Server 7.0 to a computer that is running SQL Server 7.0 and that has a different collation, follow these steps:

1. Back up the source database.

2. Use SQL Server Enterprise Manager to create scripts for all the objects in the source database.

3. To export the data from all the tables in the database, use DTS or the bcp utility.

4. Create a new database on the destination server by using SQL Server Enterprise Manager or the CREATE DATABASE statement.

Note When you use the CREATE DATABASE statement, the database will have the same collation as the computer that is running SQL Server 7.0.

5. Use SQL Query Analyzer to run the scripts that you created in step 2 to re-create all the objects in the destination database.

Note The tables and columns will have the same collation as the computer that is running SQL Server 7.0.

6. Import the data in the destination tables by using DTS or the bcp utility.

Back to the top

Transfer a database from one collation in SQL Server 7.0 to a collation in SQL Server 2000

To change the collation of a database between SQL Server 7.0 and SQL Server 2000, you must create the database, the columns, or both with the appropriate collation on the destination server before you transfer the data. However, you can use DTS to drop and then re-create the objects when you transfer data from SQL Server 7.0 to SQL Server 2000. When doing so, you must enable the Use Collation option in DTS.

Note Do not use the DTS utility that is included in SQL Server 7.0 to transfer all objects to or from a computer that is running SQL Server 2000. You must use the DTS utility that is included in SQL Server 2000 when you have to transfer data between SQL Server 7.0 and SQL Server 2000.

To transfer a database from one collation in SQL Server 7.0 to a collation in SQL Server 2000, follow these steps:

1. Back up the source database.

2. Use SQL Server Enterprise Manager to create scripts for all the objects in the source database.

3. If the columns must have a different collation than the default collation on the destination database, make the required collation changes to the appropriate columns in the scripts.

4. Create a new database on the destination server with the appropriate collation.

5. Use SQL Query Analyzer to run the scripts that you created in step 2 on the destination server to re-create all objects in the database.

Note The new tables and columns have the same collation as the database unless you specify a different collation for the columns.

6. Use DTS or the bcp utility to transfer the data.

Note If you use DTS, verify the following:

o Make sure that the Use Collation option is enabled when you transfer data from SQL Server 7.0 to SQL Server 2000.

o Because the objects are already created on the destination server with the appropriate collation, disable the Create Destination Objects First option.

Back to the top

Transfer a database from one collation in SQL Server 2000 to a different collation in SQL Server 2000

To transfer a database from one collation in SQL Server 2000 to a different collation in SQL Server 2000, follow these steps:

1. Back up the source database.

2. Note if any columns use the COLLATE clause.

3. Create a new database on the destination server with the appropriate collation.

4. If no columns use the COLLATE clause, use DTS to transfer the data to the destination server. To do so, enable the Use Collation option for code page translation and to transfer the data to the new collation on the destination database. If any columns use the COLLATE clause, follow these steps:

a. Generate scripts for all the objects (not including the indexes, the triggers, the primary keys, the foreign keys, the default settings, and the constraints). Additionally, make sure that you enable the Only script 7.0 compatible features option to remove the COLLATE clause from the script.

Note When you use the Only script 7.0 compatible features option, you can change the collation. However, any new SQL Server 2000 options (including user-defined functions, extended properties, the INSTEAD OF trigger, and indexes on views) will not be considered when the scripts are generated.

b. Run the scripts from step a on the destination database to create the objects with the destination database collation.

c. Use DTS to transfer only the data from the source database.

d. After the data is successfully transferred, generate scripts for all the constraints, foreign keys, primary keys, and indexes from the source database.

Note On the Formatting tab of the Generate SQL Scripts dialog box, click to clear the following check boxes:

§ Generate the CREATE <object> command for each object

§ Generate the DROP <object> command for each object

e. Run the scripts from step d on the destination database.

Back to the top

Change the collation of the master database

If you want to change the collation of the master database, you must rebuild the master database. When you rebuild the master database, you essentially create a new master database. Therefore, consider the following items before you rebuild the master database:

· Make sure to make a valid backup of the current master database. When you rebuild the master database, the msdb database and the model database are also re-created. Therefore, you must back up the msdb database and the model database before you rebuild the master database. The msdb database is the system database that is used to store your SQL Server jobs, alerts, operators, and DTS packages. The model database is the template database that is used when you create a new database.

· Because rebuilding the master database creates a new master database, you must re-enter the existing login information after you rebuild the master database. Therefore, you must export the login information before you rebuild the master database. After you re-create the master database, import the login information. For more information about how to export login information, click the following article number to view the article in the Microsoft Knowledge Base:

246133 How to transfer logins and passwords between instances of SQL Server

· Because the msdb database is rebuilt when you rebuild the master database, you must generate scripts for all the jobs, alerts, and operators before you rebuild the master database. Additionally, you must make sure that you move all DTS packages. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

314546 How to move databases between computers that are running SQL Server

· Because the model database is rebuilt when you rebuild the master database, any changes that were previously made to the model database must either be noted, scripted, or exported before you rebuild the master database. After the model database is rebuilt, reapply any noted changes.

Change the collation of the master database in SQL Server 2000

Before you change the collation of the master database, follow these steps:

1. When you rebuild the master database, it does not automatically recall all the databases that were created before the database is rebuilt. Therefore, all databases must be restored from a backup or if the files are still on disk, you can reattach the databases by using the sp_attach_db system stored procedure. Make sure that you have all the necessary information to reattach existing databases before you rebuild the master database. For more information about the sp_attach_db system stored procedure, visit the following MSDN Web site:

http://msdn2.microsoft.com/en-us/library/aa259611(SQL.80).aspx

2. When you rebuild the master database, only the collation of the master database, the model database, and the msdb database is changed. The collation of the user databases is not changed. To change the collation of an existing user database or to create a new database with the appropriate collation, use the ALTER DATABASE command, and then use DTS or the bcp utility to transfer the data to the new database.

Note If you use the ALTER DATABASE command in SQL Server 2000 to change the collation of a database, the collation of the columns in the tables is not automatically changed. To change the collation of the columns, use the ALTER TABLE command and the ALTER COLUMN command. If you are using DTS, you can create the table and the columns with the appropriate collation before you transfer the data or you can use the Use Collation option. If you are using DTS and the table with the appropriate collation already exists, make sure to disable the Drop Existing Objects First option before you run the package.

Change the collation of the master database in SQL Server 7.0

Before you change the collation of the master database, follow these steps:

1. SQL Server 7.0 does not support having databases with collations other than the default collation. Therefore, before you rebuild the master database, export all the data from the user databases.

2. Generate the scripts for all the objects in the database.

3. Re-create the master database with the appropriate collation.

4. Create the new databases. The new databases are automatically created with the new default collation.

5. Run the previously saved scripts to re-create the objects, and then import the data that you previously exported.

Back to the top

REFERENCES

For more information about collations, visit the following Microsoft Web sites:

· http://msdn2.microsoft.com/en-us/library/aa214408(SQL.80).aspx

· http://msdn2.microsoft.com/en-us/library/aa174903(SQL.80).aspx

· http://msdn2.microsoft.com/en-us/library/aa258237(SQL.80).aspx

How to transfer logins and passwords between instances of SQL Server

http://support.microsoft.com/kb/246133/en-us

View products that this article applies to.

This article was previously published under Q246133

On This Page

· SUMMARY

o How to transfer logins and passwords between servers that are running SQL Server 7.0

o How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that are running SQL Server 2000

o How to transfer logins and passwords between instances of SQL Server 2005

o A complete resolution to transfer logins and passwords between different versions of SQL Server

o Method 1

o Method 2

o Remarks

Expand all | Collapse all

SUMMARY

After you move databases to a new server, users may not be able to log in to the new server. Instead, they receive the following error message:

Msg 18456, Level 16, State 1
Login failed for user '%ls'.

You must transfer the logins and passwords to the new server. This article describes how you transfer logins and passwords to a new server.

Back to the top

How to transfer logins and passwords between servers that are running SQL Server 7.0

The SQL Server 7.0 Data Transformation Services (DTS) Object Transfer feature transfers logins and users between two servers, but it does not transfer the passwords for SQL Server authenticated logins. To transfer logins and passwords from a server that is running SQL Server 7.0 to another server that is running SQL Server 7.0, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.

Back to the top

How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that are running SQL Server 2000

To transfer logins and passwords from a SQL Server 7.0 server to an instance of SQL Server 2000, or between two instances of SQL Server 2000, you can use the new DTS Package Transfer Logins Task in SQL Server 2000. To do this, follow these steps:

1. Connect to the SQL Server 2000 destination server, move to the Data Transformation Services in SQL Server Enterprise Manager, expand the folder, right-click Local Packages, and then click New Package.

2. After the DTS package designer opens, click Transfer Logins Task on the Task menu. Complete the information about the Source, Destination and Logins tabs as appropriate.

Important The SQL Server 2000 destination server cannot be running the 64-bit version of SQL Server 2000. DTS components for the 64-bit version of SQL Server 2000 are not available. If you are importing logins from an instance of SQL Server that is on a separate computer, your instance of SQL Server will must be running under a Domain Account to complete the task.

Note The DTS method will transfer the passwords but not the original SID. If a login is not created by using the original SID and user databases are also transferred to a new server, the database users will be orphaned from the login. To transfer the original SID and bypass the orphaned users, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.

Back to the top

How to transfer logins and passwords between instances of SQL Server 2005

For more information about how to transfer the logins and passwords between instances of SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base:

918992 How to transfer the logins and the passwords between instances of SQL Server 2005

Back to the top

A complete resolution to transfer logins and passwords between different versions of SQL Server

To do this, use one of the following methods.
Notes

· The scripts in the following methods create two stored procedures that are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure in your master database.

· The scripts are dependent on SQL Server system tables. The structure of these tables may change between versions of SQL Server. Selecting directly from system tables is discouraged.

· Review the remarks at the end of this article for important information about the steps in the methods.

· Method 2 assigns logins to roles.

Back to the top

Method 1

This method applies to the following scenarios:

· You transfer logins and passwords from SQL Server 7.0 to SQL Server 7.0.

· You transfer logins and passwords from SQL Server 7.0 to SQL Server 2000.

· You transfer logins and passwords between servers that are running SQL Server 2000.

To transfer logins and passwords between different versions of SQL Server, follow these steps:

1. Run the following script on the source SQL Server. Continue to step 2 when you finish creating the sp_help_revlogin stored procedure.

----- Begin Script, Create sp_help_revlogin procedure -----

USE master

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

DROP PROCEDURE sp_hexadecimal

CREATE PROCEDURE sp_hexadecimal

@binvalue varbinary(256),

@hexvalue varchar(256) OUTPUT

DECLARE @charvalue varchar(256)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

DECLARE @tempint int

DECLARE @firstint int

DECLARE @secondint int

SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

SELECT @firstint = FLOOR(@tempint/16)

SELECT @secondint = @tempint - (@firstint*16)

SELECT @charvalue = @charvalue +

SUBSTRING(@hexstring, @firstint+1, 1) +

SUBSTRING(@hexstring, @secondint+1, 1)

SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

DROP PROCEDURE sp_help_revlogin

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @xstatus int

DECLARE @binpwd varbinary (256)

DECLARE @txtpwd sysname

DECLARE @tmpstr varchar (256)

DECLARE @SID_varbinary varbinary(85)

DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)

DECLARE login_curs CURSOR FOR

SELECT sid, name, xstatus, password FROM master..sysxlogins

WHERE srvid IS NULL AND name <> 'sa'

ELSE

DECLARE login_curs CURSOR FOR

SELECT sid, name, xstatus, password FROM master..sysxlogins

WHERE srvid IS NULL AND name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated '

+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

PRINT 'DECLARE @pwd sysname'

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

IF (@xstatus & 4) = 4

BEGIN -- NT authenticated account/group

IF (@xstatus & 1) = 1

BEGIN -- NT login is denied access

SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''

PRINT @tmpstr

END

ELSE BEGIN -- NT login has access

SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''

PRINT @tmpstr

END

ELSE BEGIN -- SQL Server authentication

IF (@binpwd IS NOT NULL)

BEGIN -- Non-null password

EXEC sp_hexadecimal @binpwd, @txtpwd OUT

IF (@xstatus & 2048) = 2048

SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'

ELSE

SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'

PRINT @tmpstr

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name

+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '

END

ELSE BEGIN

-- Null password

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name

+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '

END

IF (@xstatus & 2048) = 2048

-- login upgraded from 6.5

SET @tmpstr = @tmpstr + '''skip_encryption_old'''

ELSE

SET @tmpstr = @tmpstr + '''skip_encryption'''

PRINT @tmpstr

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

----- End Script -----

2. After you create the sp_help_revlogin stored procedure, run the sp_help_revlogin procedure from Query Analyzer on the source server. The sp_help_revlogin stored procedure can be used on both SQL Server 7.0 and SQL Server 2000. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password. Save the output, and then paste and run it in Query Analyzer on the destination SQL Server. For example:

EXEC master..sp_help_revlogin

Back to the top

Method 2

This method applies to the following scenarios:

· You transfer logins and passwords from SQL Server 7.0 to SQL Server 2005.

· You transfer logins and passwords from SQL Server 2000 to SQL Server 2005.

· You assign logins to roles.

To transfer logins and passwords between different versions of SQL Server and then assign logins to roles, follow these steps:

1. Run the following script on the source SQL Server.

USE master

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

DROP PROCEDURE sp_hexadecimal

CREATE PROCEDURE sp_hexadecimal

@binvalue varbinary(256),

@hexvalue varchar(256) OUTPUT

DECLARE @charvalue varchar(256)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

DECLARE @tempint int

DECLARE @firstint int

DECLARE @secondint int

SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

SELECT @firstint = FLOOR(@tempint/16)

SELECT @secondint = @tempint - (@firstint*16)

SELECT @charvalue = @charvalue +

SUBSTRING(@hexstring, @firstint+1, 1) +

SUBSTRING(@hexstring, @secondint+1, 1)

SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL

DROP PROCEDURE sp_help_revlogin_2000_to_2005

CREATE PROCEDURE sp_help_revlogin_2000_to_2005

@login_name sysname = NULL,

@include_db bit = 0,

@include_role bit = 0

DECLARE @name sysname

DECLARE @xstatus int

DECLARE @binpwd varbinary (256)

DECLARE @dfltdb varchar (256)

DECLARE @txtpwd sysname

DECLARE @tmpstr varchar (256)

DECLARE @SID_varbinary varbinary(85)

DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)

DECLARE login_curs CURSOR STATIC FOR

SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')

FROM master.dbo.sysxlogins

WHERE srvid IS NULL AND

[name] <> 'sa'

ELSE

DECLARE login_curs CURSOR FOR

SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')

FROM master.dbo.sysxlogins

WHERE srvid IS NULL AND

[name] = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr

SET @tmpstr = '** Generated '

+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

PRINT '/***** CREATE LOGINS *****/'

WHILE @@fetch_status = 0

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

IF (@xstatus & 4) = 4

BEGIN -- NT authenticated account/group

IF (@xstatus & 1) = 1

BEGIN -- NT login is denied access

SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + ''''

PRINT @tmpstr

END

ELSE

BEGIN -- NT login has access

SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'

PRINT @tmpstr

SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'

PRINT @tmpstr

END

ELSE

BEGIN -- SQL Server authentication

EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

IF (@binpwd IS NOT NULL)

BEGIN -- Non-null password

EXEC sp_hexadecimal @binpwd, @txtpwd OUT

SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'

END

ELSE

BEGIN -- Null password

SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''

END

SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string

PRINT @tmpstr

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

END

IF @include_db = 1

BEGIN

PRINT ''

PRINT '/***** SET DEFAULT DATABASES *****/'

FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

WHILE @@fetch_status = 0

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'

PRINT @tmpstr

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

END

IF @include_role = 1

BEGIN

PRINT ''

PRINT '/***** SET SERVER ROLES *****/'

FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

WHILE @@fetch_status = 0

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

IF @xstatus &16 = 16 -- sysadmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''

PRINT @tmpstr

END

IF @xstatus &32 = 32 -- securityadmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''

PRINT @tmpstr

END

IF @xstatus &64 = 64 -- serveradmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''

PRINT @tmpstr

END

IF @xstatus &128 = 128 -- setupadmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''

PRINT @tmpstr

END

IF @xstatus &256 = 256 --processadmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''

PRINT @tmpstr

END

IF @xstatus &512 = 512 -- diskadmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''

PRINT @tmpstr

END

IF @xstatus &1024 = 1024 -- dbcreator

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''

PRINT @tmpstr

END

IF @xstatus &4096 = 4096 -- bulkadmin

BEGIN

SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''

PRINT @tmpstr

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1

2. Save the output, and then paste and run the output in SQL Server Management Studio on the destination SQL Server 2005.

Note If the source SQL Server contains a login that has a blank password, the output contains a statement that resembles the following.

CREATE LOGIN LoginName WITH PASSWORD = '', CHECK_POLICY = OFF, SID = MySID

Back to the top

Remarks

· Review the output script carefully before you run it on the destination SQL Server. If you have to transfer logins to an instance of SQL Server in a different domain than the source instance of SQL Server, edit the script generated by the sp_help_revlogin procedure, and replace the domain name with the new domain in the sp_grantlogin statements. Because the integrated logins granted access in the new domain will not have the same SID as the logins in the original domain, the database users will be orphaned from these logins. To resolve these orphaned users, see the articles referenced in the following bullet item. If you transfer integrated logins between instances of SQL Servers in the same domain, the same SID is used and the user is not likely to be orphaned.

· After you move the logins, users may not have permissions to access databases that have also been moved. This problem is described as an "orphaned user". If you try to grant the login access to the database, it may fail indicating the user already exists:

Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.

For instructions about how to map the logins to the database users to resolve orphaned SQL Server logins and integrated logins, see the following article in the Microsoft Knowledge Base:

240872 How to resolve permission issues when you move a database between servers that are running SQL Server

For instructions about using the sp_change_users_login stored procedure to correct the orphaned users one-by-one (this will only address users orphaned from standard SQL logins), see the following article in the Microsoft Knowledge Base:

274188 "Troubleshooting Orphaned Users" topic in Books Online is incomplete

· If the transfer of logins and passwords is part of a move of databases to a new server running SQL Server, see the following article in the Microsoft Knowledge Base for a description of the workflow and steps involved:

314546 How to move databases between computers that are running SQL Server

· You can do this because of the @encryptopt parameter in the sp_addlogin system stored procedure, that allows a login to be created by using the encrypted password. For more information about this procedure, see the "sp_addlogin (T-SQL)" topic in SQL Server Books Online.

· By default, only members of the sysadminfixed server role can select from the sysxlogins table. Unless a member of the sysadmin role grants the necessary permissions, end users cannot create or run these stored procedures.

· This approach does not try to transfer the default database information for a particular login because the default database may not always exist on the destination server. To define the default database for a login, you can use the sp_defaultdb system stored procedure by passing it the login name and the default database as arguments. For more information about using this procedure, see the "sp_defaultdb" topic in SQL Server Books Online.

· During a transfer of logins between instances of SQL Server, if the sort order of the source server is case-insensitive and the sort order of the destination server is case-sensitive, you must enter all alphabetical characters in passwords as uppercase characters after the transfer of logins to the destination server. If the sort order of the source server is case-sensitive and the sort order of the destination server is case-insensitive, you will not be able to log in with the logins transferred using the procedure outlined in this article, unless the original password contains no alphabetical characters or unless all alphabetical characters in the original password are uppercase characters. If both servers are case-sensitive or both servers are case-insensitive, you will not experience this problem. This is a side effect of the way that SQL Server handles passwords. For more information, see the "Effect on Passwords of Changing Sort Orders" topic in SQL Server 7.0 Books Online.

· When you run the output from the sp_help_revlogin script on the destination server, if the destination server already has a login defined with the same name as one of the logins on the script output, you may see the following error upon execution of the output of the sp_help_revlogin script:

Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
The login 'test1' already exists.

Likewise, if a different login exists with the same SID value on this server as the one you are trying to add, you receive the following error message:

Server: Msg 15433, Level 16, State 1, Procedure sp_addlogin, Line 93
Supplied parameter @sid is in use.

Therefore, you must carefully review the output from these commands, examine the contents of the sysxlogins table, and address these errors accordingly.

· The SID value for a particular login is used as the basis for implementing database level access in SQL Server. Therefore, if the same login has two different values for the SID at the database level (in two different databases on that server), the login will only have access to that database whose SID matches the value in syslogins for that login. Such a situation might occur if the two databases in question have been consolidated from two different servers. To resolve this problem, the login in question would have to be manually removed from the database that has a SID mismatch by using the sp_dropuser stored procedure, and then added again by using the sp_adduser stored procedure.

Back to the top