Post reply

How to Migrate SQL Logins Between Two Azure SQL Instances Without sp_helprevlogi

  • July 25, 2024 at 6:45 pm

    #4439882

    Hello everyone,

    I am currently working on migrating SQL logins between two Azure SQL instances. Unfortunately, I am facing several limitations and would appreciate your help in finding an effective solution.

    Using sp_helprevlogin: This stored procedure is not supported in Azure SQL.

    Using dbatools: The dbatools option is not viable in my current environment.

    HASHED Option in CREATE LOGIN: When I try to use the HASHED option with CREATE LOGIN, it does not work as expected.

    Here is what I have tried so far:

    I exported the logins from the source instance using a T-SQL query to get the necessary information (login name, SID, etc.).

    I attempted to recreate the logins on the destination instance using CREATE LOGIN with the exported information, but there are issues with the passwords.

    My questions are as follows:

    What is the best method to migrate SQL logins between two Azure SQL instances without using sp_helprevlogin and without dbatools?

    How can I work around the limitations of the HASHED option with CREATE LOGIN?

    Are there any alternative scripts or tools that I could use to accomplish this task?

    Any help or suggestions would be greatly appreciated. Thank you in advance for your time and expertise.

    Best regards,

  • July 26, 2024 at 2:50 pm

    #4440087

    I have not tested this script, but what about this link:

    https://eitanblumin.com/2021/05/11/t-sql-tuesday-138-sp_help_revlogin-is-dead-long-live-sp_help_revlogin2/

    Eitan Blumin has a script that he claims works to move logins and users across different systems. NOTE I have not tested or used the script, just found it and thought I'd share. If you need to sync permissions, he links to scripts by Kenneth Fisher to do that. PLEASE read the whole blog post before you blindly run the script on your server as Eitan has some notes about the scripts limitations. Also, please test it on a test environment before running on prod... last thing you want to do is break prod by running a random script you found on the internet.

    My opinion, Powershell is the way of the future for syncing users and logins but I see it is not available in your current environment. I do recommend getting it for your environment though. It is a great tool for DBA work.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply