Lesson Learned #71: Fixing an orphaned users using a copy database process in Azure SQL Databases.
Published Mar 13 2019 07:41 PM 3,197 Views
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



Enjoy!
Version history
Last update:
‎Mar 13 2019 07:41 PM
Updated by: