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

First published on MSDN on Feb 10, 2019
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

• Manage Logins in Azure SQL Database and URLs that explain with more details the issue:
  
  
  
  • https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins
  
  
  • https://docs.microsoft.com/en-us/azure/sql-database/sql-database-control-access
  
  
  • https://docs.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-2017
  
  
  • https://docs.microsoft.com/EN-US/azure/sql-database/sql-database-copy
  
  
  • https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?view=sql-server-2017
  
  
  • https://docs.microsoft.com/en-us/azure/sql-database/sql-database-geo-replication-security-config
  
  
  
  

Enjoy!