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