Sync Login from MSSQL to Azure

lin 1 Reputation point
2021-03-24T02:32:52.357+00:00

Prior DB migration from MSSQL to Azure, I tried to sync login by using script as below CREATE LOGIN [username] WITH PASSWORD = 'password', SID = 0xxxxxxxxxxxx However I got this error : Specified SID is invalid for this version of SQL Server. Is there anyway i can create login with same SID so I no need regrant access since there is DB user mapping after migrate DB to azure?

Azure SQL Database
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
489 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. KalyanChanumolu-MSFT 8,316 Reputation points
    2021-03-25T05:22:41.983+00:00

    @lin Welcome to Microsoft Q&A forums.

    If you are using Azure Database Migration Service, you can easily migrate the logins using a graphical user interface.

    If you want to migrate logins using a TSQL script, please use MoveLogin.ps1 to generate it.
    The tool allows the automatic mapping of Windows AD accounts to Azure AD accounts or it can do UPN lookups for each login against the on-premise Windows Active Directory.

    ----------

    If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

  2. CarrinWu-MSFT 6,851 Reputation points
    2021-03-25T07:02:42.967+00:00

    Hi @lin ,

    The SID is not possible to transfer between OnPremise environment to Azure SQL Database. You will get the error message if you execute the T-SQL. Please refer to Lesson Learned #35: How to transfer the login and user from OnPremise to Azure SQL Database to get more information.

    You can move SQL logins from the source SQL Server to Azure SQL Database using Database Migration Service (DMS) in offline mode. Use the Selected logins blade in the Migration Wizard to migrate logins to your target SQL Database. Please refer to Logins and groups to get more information.

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments