Home
%3CLINGO-SUB%20id%3D%22lingo-sub-369196%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2371%3A%20Fixing%20an%20orphaned%20users%20using%20a%20copy%20database%20process%20in%20Azure%20SQL%20Databases.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369196%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Feb%2010%2C%202019%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20Hello%20Team%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Some%20days%20ago%2C%20I%20worked%20in%20a%20service%20request%20in%20this%20following%20scenario%3A%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EUsing%20the%20portal%2C%20our%20customer%2C%20copied%20the%20database%20from%20one%20server%20to%20another.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EAfter%20it%2C%20our%20customer%20was%20not%20able%20to%20connect%20to%20the%20copied%20database%20in%20the%20destination%20server.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CSTRONG%3E%20I%20would%20like%20to%20explain%20why%2C%20performing%20the%20following%20example%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20I%20created%20two%20servers%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EJMSource%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EJMTarget%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20In%20the%20server%20JMSource%20I%20created%20a%20database%20called%20CopyDatabase%20%3C%2FSTRONG%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20In%20the%20master%20database%20of%20JMSource%20server%20I%20created%20two%20logins%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3ECREATE%20LoginA%20WITH%20PASSWORD%3D'%3CCOMPLEXPWD%3E'%3C%2FCOMPLEXPWD%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ECREATE%20LoginB%20WITH%20PASSWORD%3D'%3CCOMPLEXPWD%3E'%3C%2FCOMPLEXPWD%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20In%20the%20CopyDatabase%20database%20of%20JMSource%20server%20I%20created%204%20users%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3ETwo%20users%20that%20are%20associated%20with%20Logins%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3ECREATE%20USER%20LoginA%20FOR%20LOGIN%20LoginA%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ECREATE%20USER%20LoginB%20FOR%20LOGIN%20LoginB%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EFor%20both%20users%2C%20I%20applied%20the%20db_owner%20permission%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EEXEC%20Sp_addrolemember%20%E2%80%98db_owner%E2%80%99%2C%E2%80%99LoginA%E2%80%99%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EEXEC%20Sp_addrolemember%20%E2%80%98db_owner%E2%80%99%2C%E2%80%99LoginA%E2%80%99%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ETwo%20contained%20users%20(not%20logins%20associated)%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3ECREATE%20USER%20USER1%20WITH%20PASSWORD%3D'%3CCOMPLEXPWD%3E'%3C%2FCOMPLEXPWD%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ECREATE%20USER%20USER1%20WITH%20PASSWORD%3D'%3CCOMPLEXPWD%3E'%3C%2FCOMPLEXPWD%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EFor%20both%20users%2C%20I%20applied%20the%20db_owner%20permission%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EEXEC%20Sp_addrolemember%20%E2%80%98db_owner%E2%80%99%2C%E2%80%99User1%E2%80%99%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EEXEC%20Sp_addrolemember%20%E2%80%98db_owner%E2%80%99%2C%E2%80%99User2%E2%80%99%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89113i6E13E0C0AC8957C7%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EUsing%26nbsp%3Bthe%20portal%20or%20running%20the%20TSQL%26nbsp%3BCREATE%20DATABASE%20CopyDatabase_Copy%20AS%20COPY%20OF%20JmSource.CopyDatabase%2C%20I%20copied%20the%20database%20from%20JmSource%20to%20JmTarget%20server.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20We%20have%20the%20following%20scenario%20when%20our%20customer%20tries%20to%20connect%20to%20this%20database%20in%20JMTarget%20server%20using%20SQL%20Server%20Management%20Studio%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EI%20cannot%20to%20the%20master%20database%20because%20User1%20and%20User2%20have%20been%20created%20specifically%20in%20CopyDatabase.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EUsing%20the%20User1%20and%20User2%20that%20are%20Contained%20Database%20(they%20don't%20have%20any%20login%20associated)%3A%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89114iB05FE940A6AA27F0%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89115i18B12EEC6AC0B294%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20So%2C%20I%20need%20to%20change%20the%20connection%20string%20of%20SQL%20Server%20Management%20Studio%20to%20be%20able%20to%20connect.%20%3C%2FSTRONG%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89116i17DD5C3B86FEE8B8%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20But%2C%20what%20is%20happening%20with%20LoginA%20and%20LoginB%3F%20Why%26nbsp%3Bthese%20users%20are%20not%20able%20to%20connect%20even%20changing%20the%20connection%20string%3F%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EThis%20issue%20is%20because%20we%20don't%20have%20created%20the%20Login%20in%20Master%20database%20of%20JMTarget%20database.%20Remember%20that%20when%20you%26nbsp%3Brun%20a%20copy%20database%20all%20the%20objects%20plus%20users%20will%20be%20copied%20but%20as%20the%20logins%20are%20saved%20on%20master%20database%20of%20source%20server%20these%20will%20be%20not%20copied.%20So%2C%20what%20I%20do%20need%20to%20fix%20it%3F%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3EFirst%2C%20%3C%2FSTRONG%3E%20I%20need%20to%20create%20the%20login%20on%20master%20database%20of%20JMTarget%2C%20but%2C%20using%20the%20same%20SID.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3ESecond%2C%20%3C%2FSTRONG%3E%20connected%20to%20the%20database%20CopyDatabase_Copy%20of%20JMTarget%20I%20executed%20the%20following%20TSQL%3ASELECT%20%5Bname%5D%2C%20%5Bsid%5D%20FROM%20%5Bsys%5D.%5Bdatabase_principals%5D%20WHERE%20%5Btype_desc%5D%20%3D%20'SQL_USER'%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3EThird%2C%20%3C%2FSTRONG%3E%20I%20obtained%20two%20SIDs%20for%20LoginA%20and%20LoginB%20with%20these%20codes%2C%20I'm%20going%20to%20create%20the%20logins%20in%20master%20database%20of%20this%20JMTarget%20server%2C%20running%20the%20following%20commands%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3ECREATE%20LoginA%20WITH%20PASSWORD%3D'%3CCOMPLEXPWD%3E'%2C%20SID%3D0x01060000....%3C%2FCOMPLEXPWD%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ECREATE%20LoginB%20WITH%20PASSWORD%3D'%3CCOMPLEXPWD%3E'%2C%20SID%3D0x01060001....%3C%2FCOMPLEXPWD%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89117iAD261F281EF5EFCC%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3E%20And%20what%20happens%20if%20I%20create%20only%20the%20Login%20without%26nbsp%3Bspecifying%20the%20SID%3F%20In%20this%20situation%2C%20you%20are%20going%20to%20have%20the%20following%20error%3A%20%3C%2FSTRONG%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89118i53E9526A06D72422%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CH2%20id%3D%22next-steps%22%20id%3D%22toc-hId-1679020792%22%20id%3D%22toc-hId-1701994174%22%3ENext%20steps%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-manage-logins%23next-steps%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20%3C%2FA%3E%3C%2FH2%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EManage%20Logins%20in%20Azure%20SQL%20Database%20and%20URLs%20that%20explain%20with%20more%20details%20the%20issue%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-manage-logins%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-manage-logins%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-control-access%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-control-access%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsecurity%2Fcontained-database-users-making-your-database-portable%3Fview%3Dsql-server-2017%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsecurity%2Fcontained-database-users-making-your-database-portable%3Fview%3Dsql-server-2017%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2FEN-US%2Fazure%2Fsql-database%2Fsql-database-copy%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20https%3A%2F%2Fdocs.microsoft.com%2FEN-US%2Fazure%2Fsql-database%2Fsql-database-copy%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fsql-server%2Ffailover-clusters%2Ftroubleshoot-orphaned-users-sql-server%3Fview%3Dsql-server-2017%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fsql-server%2Ffailover-clusters%2Ftroubleshoot-orphaned-users-sql-server%3Fview%3Dsql-server-2017%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-geo-replication-security-config%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-geo-replication-security-config%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20Enjoy!%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-369196%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Feb%2010%2C%202019%20Hello%20Team%2CSome%20days%20ago%2C%20I%20worked%20in%20a%20service%20request%20in%20this%20following%20scenario%3A%20Using%20the%20portal%2C%20our%20customer%2C%20copied%20the%20database%20from%20one%20server%20to%20another.%3C%2FLINGO-TEASER%3E
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!