I'm having trouble copying database users from one Azure SQL Server to another. The EXECUTE permission was denied

Question

I'm trying to use the instructions here -

https://learn.microsoft.com/en-US/troubleshoot/sql/database-engine/security/transfer-logins-passwords-between-instances

to copy logins between the server which hosts my primary DB and the server which hosts my read replica. This is important for me because I want all my users who are currently querying the production DB to start querying the read replica instead.

I am able to create the sprocs in the master database on the primary server, but when I attempt to execute the sproc, as per the instructions, I receive the following error:

EXEC sp_help_revlogin

Msg 229, Level 14, State 5, Procedure sp_help_revlogin, Line 1 [Batch Start Line 0] The EXECUTE permission was denied on the object 'sp_help_revlogin', database 'master', schema 'dbo'.

enter image description here

How do I get past this?

I'm using Azure's hosted SQL Database and I'm logged into the server as the Administrator (using the account defined as the Microsoft Entra Admin).

Thanks for your advice!

Answer 1

The steps you are using to transfer logins between Azure SQL logical servers apply only to Microsoft SQL Server instances and do not apply to Azure SQL databases.

To my knowledge there is no way to transfer logins to existing Azure SQL database. Even when we are configuring Azure SQL Geo-replication we need to manually make sure all the logins exist on the replica databases, if they do not exist we need to manually create them.

If you would like to vote for this feature to be available on Azure SQL Database in the future please vote here.

• Yossi Geretz: Thanks! I upvoted that request. It's from 6 years ago so I'm not holding my breath. The problem with recreating the logins is that invariably this would have to involve issuing everyone new passwords. A pain! I think I'm going to move to Azure AD integration for user accounts. That should reduce this maintenance burden.
• Alberto Morillo: Azure AD (Entra) adds MFA. The trend is avoiding SQL authentication.
• Yossi Geretz: We ended up going with Microsoft Entra authentication. No more SQL accounts! (Well, we have a few service accounts, but we got rid of all the accounts we used to maintain for interactive user access.)

Answer 2

As Alberto said in the other answer, the sp_help_revlogin procedure was meant for SQL Server instances, not Azure SQL Databases.

Also, due to security (and syntax) limitations in Azure SQL Databases, it wouldn't be possible to export or import the hashed passwords of logins, therefore you would be forced regardless to re-specify the passwords explicitly when re-creating the logins.

There are other options that have better support for Azure SQL Databases to generate logins, users, and their roles and permissions.

Please see these two for reference, based on stored procedures created by Kenneth Fisher, and adapted for Azure SQL Databases by Madeira Data Solutions:

• sp_GetSrvPermissions.sql (run on master)
• sp_GetDBPermissions.sql (run on the user database)

Also regardless regardless, moving on to AzureAD / EntraID authentication instead of SQL Authentication is probably the preferable method anyway.