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


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!


Comments (0)

Skip to main content