Error : Insufficient permission to create a database copy on server on Azure SQL database

Question

Copy Azure SQL database to a different subscription fails with below error

Error : Insufficient permission to create a database copy on server XXXXXXXXXX

I have followed the exact same steps below as mentioned on MS documentation as below even though it fails. I am the one who created the source database.

Import/Export Data Tier(Bacpac) is not an option due to the huge Size of the Azure SQL database.

Is there any way to do this faster?

--Step# 1
--Create login and user in the master database of the source server.

CREATE LOGIN loginname WITH PASSWORD = 'xxxxxxxxx'
GO
CREATE USER [loginname] FOR LOGIN [loginname] WITH DEFAULT_SCHEMA=[dbo];
GO
ALTER ROLE dbmanager ADD MEMBER loginname;
GO

--Step# 2
--Create the user in the source database and grant dbowner permission to the database.

CREATE USER [loginname] FOR LOGIN [loginname] WITH DEFAULT_SCHEMA=[dbo];
GO
ALTER ROLE db_owner ADD MEMBER loginname;
GO

--Step# 3
--Capture the SID of the user "loginname" from master database

SELECT [sid] FROM sysusers WHERE [name] = 'loginname';

--Step# 4
--Connect to Destination server.
--Create login and user in the master database, same as of the source server.

CREATE LOGIN loginname WITH PASSWORD = 'xxxxxxxxx', SID = [SID of loginname login on source server];
GO
CREATE USER [loginname] FOR LOGIN [loginname] WITH DEFAULT_SCHEMA=[dbo];
GO
ALTER ROLE dbmanager ADD MEMBER loginname;
GO

--Step# 5
--Execute the copy of database script from the destination server using the credentials created

CREATE DATABASE new_database_name
AS COPY OF source_server_name.source_database_name;

Answer 1

I had a similar problem - My issue was the use of a VPN ( Tailscale) on the client. So I had to disable the VPN - add my private IP to both SQL Servers firewalls - then the copy worked.
Not the best error message in the world !
SO for anyone that ends up here - the issue is likely to be your networking - rather than any sql permissions issue .

Answer 2

@RobertoBustos-1154- Finally i did figure it out. It was because from the source I was connecting from public endpoint as it has no Private IP.

The destination has Private IP enabled, so i disconnected from my VPN ,added my public IP to the destination again. Copy then worked succesfully.

Thanks for your help.

Answer 3

@Tonney Vincent Do you have private endpoints enabled in the source subscription or in the target?
If private endpoint is configured but public network access is allowed, initiating database copy when connected to the destination server from a public IP address will succeed.
https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell#copy-using-transact-sql

Also This error could occurs when the user initiating the copy is not either server admin or a member of the db_owner role on the source database.