Creating Logins for Databases Restored to a Replacement SQL Server
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.
- 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.
— Creates table Logins in Northwind
— containing all SQL Logins on the server
— (but not ‘sa’, ‘guest’, or ‘distributor_admin’)
— 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]
— Insert information about the logins into table logins.
SELECT name, [password], dbname, language, sid,
ORDER BY name
— Remove special SQL logins and all Windows logins.
WHERE loginname IN (‘distributor_admin’, ‘guest’, ‘sa’)
OR loginname LIKE ‘%%’
— Look at the results.
SELECT name, defaultdb FROM logins