"Login failed for user..." - 'orphaned users' problem
Published 13 March 2017
SQL Clone operates by attaching databases; when you are attaching a database to a new server, this may expose you to a common issue with SQL Server logins and their internal references on the SQL Server you are delivering a database to.
Scenario
I have 2 SQL Servers; RM-IClone1 and RM-IClone2. In each I create a SQL Server login and map it to a user in my Forex database.
On each server, I run the following to create a SQL Server Login and matching user in the Forex database
USE [master] GO CREATE LOGIN [OneSQLServerUser] WITH PASSWORD=N'MyC0mpliantPa$$word', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [Forex] GO CREATE USER [OneSQLServerUser] FOR LOGIN [OneSQLServerUser] GO ALTER ROLE [db_owner] ADD MEMBER [OneSQLServerUser] GO
I then create a SQL Clone Image from the Forex database on RM-IClone1, and deliver it as a clone database to RM-IClone2.
Running an identical query on the original Forex database and on its clone database I see the following;
The solution is to recreate the login on the target SQL Server, but with a matching SID to the source server.
First, obtain the SID that is to be used:
-- On the source SQL Server from which the SQL Clone image was created USE Forex GO SELECT name, sid, principal_id FROM sys.database_principals WHERE Name = 'OneSQLServerUser'
Next, on the server you have delivered the clone database to, run the following to recreate the login and remap the user account:
-- On the target SQL Server to which a clone database has been delivered USE master go DROP LOGIN OneSQLServerUser; GO CREATE LOGIN OneSQLServerUser WITH PASSWORD = 'MyC0mpliantPa$$word', SID = 0x3D195F3CF7D2D443974CBAF65BF7E479; GO USE _Forex_SiD GO ALTER USER OneSQLServerUser WITH Login = OneSQLServerUser;
As this is a server-level operation - updating the SID of the SQL Server login - you should only have to do this once for each server you are delivering the clone databases to. Subsequent clone databases will map the user account to the login correctly.
Full details are in this MSDN article - https://msdn.microsoft.com/en-GB/library/ms175475.aspx