Lesson Learned #71: Fixing an orphaned users using a copy database process in Azure SQL Databases.

This post has been republished via RSS; it originally appeared at: MSDN Blogs.

Hello Team,

Some days ago, I worked in a service request in this following scenario:

Using the portal, our customer, copied the database from one server to another.
After it, our customer was not able to connect to the copied database in the destination server.

I would like to explain why, performing the following example:

I created two servers:
- JMSource
- JMTarget
In the server JMSource I created a database called CopyDatabase
In the master database of JMSource server I created two logins:
- CREATE LoginA WITH PASSWORD='<ComplexPwd$1>'
- CREATE LoginB WITH PASSWORD='<ComplexPwd$2>'
In the CopyDatabase database of JMSource server I created 4 users:
- Two users that are associated with Logins:
  - CREATE USER LoginA FOR LOGIN LoginA
  - CREATE USER LoginB FOR LOGIN LoginB
  - For both users, I applied the db_owner permission:
    - EXEC Sp_addrolemember ‘db_owner’,’LoginA’
    - EXEC Sp_addrolemember ‘db_owner’,’LoginA’
- Two contained users (not logins associated):
  - CREATE USER USER1 WITH PASSWORD='<ComplexPwd$1>'
  - CREATE USER USER1 WITH PASSWORD='<ComplexPwd$2>'
  - For both users, I applied the db_owner permission:
    - EXEC Sp_addrolemember ‘db_owner’,’User1’
    - EXEC Sp_addrolemember ‘db_owner’,’User2’

Using the portal or running the TSQL CREATE DATABASE CopyDatabase_Copy AS COPY OF JmSource.CopyDatabase, I copied the database from JmSource to JmTarget server.
We have the following scenario when our customer tries to connect to this database in JMTarget server using SQL Server Management Studio:
- I cannot to the master database because User1 and User2 have been created specifically in CopyDatabase.
- Using the User1 and User2 that are Contained Database (they don't have any login associated):

So, I need to change the connection string of SQL Server Management Studio to be able to connect.

But, what is happening with LoginA and LoginB? Why these users are not able to connect even changing the connection string?
- This issue is because we don't have created the Login in Master database of JMTarget database. Remember that when you run a copy database all the objects plus users will be copied but as the logins are saved on master database of source server these will be not copied. So, what I do need to fix it?
  - First, I need to create the login on master database of JMTarget, but, using the same SID.
  - Second, connected to the database CopyDatabase_Copy of JMTarget I executed the following TSQL:SELECT [name], [sid] FROM [sys].[database_principals] WHERE [type_desc] = 'SQL_USER'
  - Third, I obtained two SIDs for LoginA and LoginB with these codes, I'm going to create the logins in master database of this JMTarget server, running the following commands:
    - CREATE LoginA WITH PASSWORD='<ComplexPwd$1>', SID=0x01060000....
    - CREATE LoginB WITH PASSWORD='<ComplexPwd$2>', SID=0x01060001....

And what happens if I create only the Login without specifying the SID? In this situation, you are going to have the following error:

Next steps

Enjoy!