Insufficient Permissions Error with Azure Service Principal on CREATE DATABASE AS COPY OF

Question

Insufficient Permissions Error with Azure Service Principal on CREATE DATABASE AS COPY OF

55671899 0

An attempt is being made to execute the CREATE DATABASE AS COPY OF statement that references a source database on the same Azure SQL Server and elastic pool.

The implementation uses an external login mapping to an Azure app registration
The login has dbmanager permission on the Azure SQL Server
User has db_owner permission on the source Azure SQL database.

However, the execution fails despite these configurations.

The following script has been used:

CREATE LOGIN [azure-app-registation] FROM EXTERNAL PROVIDER
GO

USE [master] 
GO 
EXEC sp_addrolemember N'dbmanager', N'user1'
GO

USE [source-db] 
GO 
EXEC sp_addrolemember N'db_owner', N'user1'
GO

CREATE DATABASE [source-db-new] AS COPY OF [source-db] (SERVICE_OBJECTIVE = ELASTIC_POOL(NAME = "my-elasticpool"))
GO

The error message received is:
Msg 45137, Level 16, State 1, Line 1 Insufficient permission to create a database copy on server '<servername>'. Ensure that the user login '<azure client id>@<azure tenant id>' has the correct permissions on the source and target servers.

The source db was not created by same login and so if I try to do this it let me run that but it is not expected behavior:

ALTER AUTHORIZATION ON DATABASE::[soruce-db] TO [azure-app-registation]

Azure reference: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=azuresqldb-current&preserve-view=true&tabs=sqlpool#permissions-1

Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff

2025-04-24T15:19:19.1966667+00:00

Hi 55671899,

Greetings!

Could you please clarify whether this issue originated in the Azure SQL Database or the on-premises SQL Server?
55671899 0 Reputation points

2025-04-24T16:42:22.2466667+00:00

Azure SQL
Sai Raghunadh M 3,315 Reputation points Microsoft External Staff

2025-04-24T18:19:52.7066667+00:00

Hi 55671899,

The error you're encountering, "Insufficient permission to create a database copy," typically arises due to the permissions required for the CREATE DATABASE AS COPY OF statement. Even though the login has dbmanager permissions on the Azure SQL Server and db_owner permissions on the source database, additional considerations might be necessary.

Here are some potential solutions and insights:

Server-Level Permissions:

Ensure that the Azure app registration (service principal) has the necessary permissions at the server level. Specifically, it might require the Contributor role on the Azure SQL Server to manage resources like databases and elastic pools.

Elastic Pool Permissions:

If the target database is being added to an elastic pool, the service principal might need permissions to manage the elastic pool. This could involve assigning the Contributor role at the resource group level or directly on the elastic pool.

ALTER AUTHORIZATION:

As you mentioned, using ALTER AUTHORIZATION to transfer ownership of the source database to the Azure app registration can resolve the issue. However, this approach changes the ownership of the database, which might not be desirable in all scenarios.

Cross-Subscription or Cross-Tenant Scenarios:

If the source and target servers are in different subscriptions or tenants, ensure that the service principal has the required permissions in both environments.

Azure Active Directory (AAD) Integration:

Verify that the Azure app registration is correctly configured in Azure Active Directory and that it has been granted the necessary roles and permissions.
Please go through these Documentations that might help you:

https://learn.microsoft.com/en-us/answers/questions/491412/error-insufficient-permission-to-create-a-database

https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?view=azuresql&tabs=azure-powershell#copy-using-transact-sql

https://stackoverflow.com/questions/78907371/permissions-for-create-database-as-copy-of
Hope this helps. Do let us know if you any further queries.
55671899 0 Reputation points

2025-04-24T19:05:02.05+00:00
If this helps you further:

If I am trying to create new database with same service principle, just by running
CREATE DATABASE [demo-db] it does not complain and create new database

If I try same but creating database in elastic pool, it still let service principle create the database
CREATE DATABASE [demo-db] (SERVICE_OBJECTIVE = ELASTIC_POOL(NAME = "my-elasticpool") )

If I try to run the CREATE DATABASE [demo-db-new] AS COPY OF [demo-db] (SERVICE_OBJECTIVE = ELASTIC_POOL(NAME = "my-elasticpool")) after running create db in step 1, it even lets me do that.

In this case, the source and target db both are on same sql server and source db is also on the same elastic pool.

I dont see any justification for giving contributor permission on sql server or elastic pool since create db could also be ran by SQL login and sql logins would not have any presence outside sql server so they should be able to create database or alter it without any issues.
Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff

2025-04-25T11:42:09.6166667+00:00

Hi @55671899,

Thank you for the details.

Could you please verify whether your client is using a private or public IP address with respect to the firewall configuration?

The source IP should match the destination IP as defined in the firewall rules. If they do not align, kindly disconnect the VPN and ensure that appropriate firewall rules are configured on both SQL Servers to allow access.
55671899 0 Reputation points

2025-04-25T14:03:00.8633333+00:00

An attempt is being made to execute the CREATE DATABASE AS COPY OF statement that references a source database on the same Azure SQL Server and elastic pool.
PratikLad 960 Reputation points Microsoft External Staff

2025-04-28T12:25:18.76+00:00
Hi @55671899,

To create a database copy, you can use a Microsoft Entra ID login that is a member of a group designated as the server administrator on both logical servers

To create a database copy, you need to be in the following roles:

Subscription Owner or

SQL Server Contributor role or

Custom role on the source logical server with following permissions: Microsoft.Sql/servers/databases/read Microsoft.Sql/servers/databases/write and

Custom role on the target logical server with following permissions: Microsoft.Sql/servers/read Microsoft.Sql/servers/databases/read `Microsoft.Sql/servers/databases/write

As you mention when you alter the authorization of the database you are able to create the copy of database.

The solution can be you can alter the authorization of the database for some time you can add the service principal as Entra admin in server.
PratikLad 960 Reputation points Microsoft External Staff

2025-04-29T09:29:39.4566667+00:00

Hi @55671899, We haven’t heard from you on the last response and wanted to follow up to check if your issue has been resolved.

If you have found a solution, we would appreciate it if you could share it with the community, as it may be helpful to others. Otherwise, please provide more details, and we will do our best to assist you further.
55671899 0 Reputation points

2025-04-29T12:17:46.0033333+00:00

@PratikLad The entire DB authorization or worst server administration is too powerful. Since the solution you presented only talks about permission a login would need at azure IAM. So the same results cannot be achieved by SQL logins I presume?
PratikLad 960 Reputation points Microsoft External Staff

2025-04-29T14:16:10.7766667+00:00

Hi @55671899 To copy database using SQL Login it should have server administrator login privileges into the master database

1 answer

Your answer

Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff

2025-04-24T15:19:19.1966667+00:00

Hi 55671899,

Greetings!

Could you please clarify whether this issue originated in the Azure SQL Database or the on-premises SQL Server?
55671899 0 Reputation points

2025-04-24T16:42:22.2466667+00:00

Azure SQL
55671899 0 Reputation points

2025-04-24T19:05:02.05+00:00

If this helps you further:

If I am trying to create new database with same service principle, just by running
CREATE DATABASE [demo-db] it does not complain and create new database

If I try same but creating database in elastic pool, it still let service principle create the database
CREATE DATABASE [demo-db] (SERVICE_OBJECTIVE = ELASTIC_POOL(NAME = "my-elasticpool") )

If I try to run the CREATE DATABASE [demo-db-new] AS COPY OF [demo-db] (SERVICE_OBJECTIVE = ELASTIC_POOL(NAME = "my-elasticpool")) after running create db in step 1, it even lets me do that.

In this case, the source and target db both are on same sql server and source db is also on the same elastic pool.

I dont see any justification for giving contributor permission on sql server or elastic pool since create db could also be ran by SQL login and sql logins would not have any presence outside sql server so they should be able to create database or alter it without any issues.
Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff

2025-04-25T11:42:09.6166667+00:00

Hi @55671899,

Thank you for the details.

Could you please verify whether your client is using a private or public IP address with respect to the firewall configuration?

The source IP should match the destination IP as defined in the firewall rules. If they do not align, kindly disconnect the VPN and ensure that appropriate firewall rules are configured on both SQL Servers to allow access.
55671899 0 Reputation points

2025-04-25T14:03:00.8633333+00:00

An attempt is being made to execute the CREATE DATABASE AS COPY OF statement that references a source database on the same Azure SQL Server and elastic pool.
PratikLad 960 Reputation points Microsoft External Staff

2025-04-28T12:25:18.76+00:00

Hi @55671899,

To create a database copy, you can use a Microsoft Entra ID login that is a member of a group designated as the server administrator on both logical servers

To create a database copy, you need to be in the following roles:

Subscription Owner or

SQL Server Contributor role or

Custom role on the source logical server with following permissions: Microsoft.Sql/servers/databases/read Microsoft.Sql/servers/databases/write and

Custom role on the target logical server with following permissions: Microsoft.Sql/servers/read Microsoft.Sql/servers/databases/read `Microsoft.Sql/servers/databases/write

As you mention when you alter the authorization of the database you are able to create the copy of database.

The solution can be you can alter the authorization of the database for some time you can add the service principal as Entra admin in server.
PratikLad 960 Reputation points Microsoft External Staff

2025-04-29T09:29:39.4566667+00:00

Hi @55671899, We haven’t heard from you on the last response and wanted to follow up to check if your issue has been resolved.

If you have found a solution, we would appreciate it if you could share it with the community, as it may be helpful to others. Otherwise, please provide more details, and we will do our best to assist you further.
55671899 0 Reputation points

2025-04-29T12:17:46.0033333+00:00

@PratikLad The entire DB authorization or worst server administration is too powerful. Since the solution you presented only talks about permission a login would need at azure IAM. So the same results cannot be achieved by SQL logins I presume?
PratikLad 960 Reputation points Microsoft External Staff

2025-04-29T14:16:10.7766667+00:00

Hi @55671899 To copy database using SQL Login it should have server administrator login privileges into the master database

Answer 1

PratikLad 960 Microsoft External Staff

Hi @55671899,

To Copy azure SQL database to an elastic pool the identity (<azure client id>@<azure tenant id>) must meet these conditions:

On the Source Server (where the database is being copied from):

The identity user must have the dbmanager role at the in the master database.
The identity user Must have the db_owner role at the in the source database.

Steps to Copy azure sql database to an elastic pool:

--Step# 1  
--Create login and user in the master database of the source server.  
CREATE LOGIN [azure-app-registation] FROM  EXTERNAL PROVIDER
GO  
CREATE USER [azure-app-registation] FOR LOGIN [azure-app-registation];  
GO  
ALTER ROLE dbmanager ADD MEMBER [azure-app-registation]a;  
GO  
  
--Step# 2  
--Create the user in the source database and grant dbowner permission to the database.  
CREATE USER [azure-app-registation] FOR LOGIN [azure-app-registation];   
GO  
ALTER ROLE db_owner ADD MEMBER [azure-app-registation];  
GO  
--Step# 3
--Sign in to the `master` database with the login that created the database you want to copy.
CREATE  DATABASE Database2 AS COPY OF Database1 (SERVICE_OBJECTIVE = ELASTIC_POOL( name = pool1 ));

Here is my Output:

enter image description here

PratikLad 960 Reputation points Microsoft External Staff

2025-05-01T12:28:44.1066667+00:00

Hi @55671899, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution

please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
PratikLad 960 Reputation points Microsoft External Staff

2025-05-02T07:41:39.2566667+00:00

Hi @55671899, We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution

please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Insufficient Permissions Error with Azure Service Principal on CREATE DATABASE AS COPY OF

1 answer

Your answer