web traffic with minimal interruption, we planned to backup all ten production databases and restore them to a temporary replacement server running SQL Server 2000. All these databases have many users. The users are mapped to their SQL logins by the security identification number (SID). When a database is restored to a different SQL server, the target server either does not have the logins to which the database users are mapped, or, if the logins have been created on the target server, the SID of a newly created SQL login will be different from the SID associated with the database user. The users are still in the database’s sysusers table, but they do not show up in Enterprise Manager. This situation is termed “Orphaned Users.” One solution to this problem uses the stored procedure sp_change_users_login and is outlined in Knowledge Base article Q274188. However, this solution must be applied on a database-by-database basis. A procedure has been devised that copies all the SQL logins (but not ‘sa’, ‘guest’, or other special SQL logins) from the source server to the target server. A test has shown that once the logins are transferred, the database users “show up” in Enterprise Manager, and any of these logins, connecting to the server, can execute procedures or T-SQL commands in the database. Their passwords have been preserved, and the logins’ permissions and roles in each database have been preserved.
Procedure
- The logins and their encrypted passwords, default databases and SIDs are copied to a table in the Northwind database. (This database was chosen because it already exists when SQL is installed.) The procedure to do this, Create.LoginsTable.sql, is shown below.
- The next step is to DTS the Logins table from <Source>.Northwind to <Target>.Northwind.
- In parallel, restore all databases to the target server. This must be completed before creating the logins, since a login will not be created if its default database does not exist.
- After all databases are restored, create the logins using the Create.logins.sql procedure, which is shown below.
Create.LoginsTable.sql
— Creates table Logins in Northwind
— containing all SQL Logins on the server
— (but not ‘sa’, ‘guest’, or ‘distributor_admin’)
USE Northwind
GO
— Create table Logins in Nortwind
CREATE TABLE [dbo].[Logins] (
[Name] [varchar] (30) NULL ,
[EncryptedPassword] [nvarchar] (128) NULL ,
[DefaultDB] [nvarchar] (128) NULL ,
[DefLanguage] [nvarchar] (128) NULL ,
[sid] [varbinary] (85) NULL ,
[EncryptOpt] [varchar] (30) NULL ,
[LoginName] [varchar] (50) NULL
) ON [PRIMARY]
GO
— Insert information about the logins into table logins.
INSERT logins
SELECT name, [password], dbname, language, sid,
‘skip_encryption’, loginname
FROM master..syslogins
ORDER BY name
GO
— Remove special SQL logins and all Windows logins.
dDELETE logins
WHERE loginname IN (‘distributor_admin’, ‘guest’, ‘sa’)
OR loginname LIKE ‘%%’
GO
— Look at the results.
SELECT name, defaultdb FROM logins