Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Wednesday, March 14, 2018 6:33 PM
All,
I tried copying the Azure SQL Database from our Production Azure SQL instance onto our Azure SQL QA instance but I'm getting the below error.
Msg 45137, Level 16, State 1, Line 2
Insufficient permission to create a database copy on server 'azsqlvmprod2'.
Why am I getting this error and why am I getting this error on the Production instance when I'm running this on the QA instance?
CREATE DATABASE web_Front_Prod2 AS COPY OF azsqlvmprod2.web_Front ( SERVICE_OBJECTIVE = 'S2' ) ;
grajee
All replies (8)
Wednesday, March 14, 2018 7:12 PM
I believe that the message "Insufficient permission to create a database copy " is indicating you don;t have hte right permissions to create the copy operation, not to create the DB itself. The account you connect to the destination server with to run the command must also exist on the source server with the same username and password.
Sam Cogan Microsoft Azure MVP
Blog | Twitter
Wednesday, March 14, 2018 7:29 PM
The account (azusdba) that I use to connect exists on both the Azure SQL Instances (QA and Prod) and on both the instances it has the role dbmanager and loginmanager.
My other question is when I'm running the script in QA why is the error referring to 'azsqlvmprod2'
Insufficient permission to create a database copy on server 'azsqlvmprod2'.
I would understand if the error was
Insufficient permission to create a database copy on server 'azsqlvmQA'.
Thanks,
grajee
Thursday, March 15, 2018 4:10 AM
Hello,
Have you tried to do the same using Azure portal or PowerShell as a workaround?
/en-us/azure/sql-database/sql-database-copy#copy-a-database-by-using-the-azure-portal
/en-us/azure/sql-database/sql-database-copy#copy-a-database-by-using-powershell
Another workaround is to make use of export and import options.
/en-us/azure/sql-database/sql-database-export#export-to-a-bacpac-file-using-the-azure-portal
/en-us/azure/sql-database/sql-database-import#import-from-a-bacpac-file-using-azure-portal
Hope this helps.
Regards,
Alberto Morillo
SQLCoffee.com
Thursday, March 15, 2018 8:00 AM
That would be the source of your issue. The user account you are using to do the copy, "azusdba" does not have permissions on the source DB.
Sam Cogan Microsoft Azure MVP
Blog | Twitter
Thursday, March 15, 2018 1:59 PM
I checked the permissions and roles when I first got the error. azusdba is a db_owner in master and web_Front databases of azuresqlvmprod2.
Monday, May 28, 2018 2:47 PM
I had a similar problem and the issue was the firewall settings for Azure SQL server in the portal.
In my case, even though the Virtual Network was added to firewall, I still had to add the local IP address of the VM to the firewall settings in Azure Portal.
You can start troubleshooting by going to Azure Portal -> SQL Servers -> Firewalls and virtual networks and adding temporarily access for the whole world - 0.0.0.0 - 255.255.255.255 and see if the error persists.
Kind regards,
Andrzej
Friday, March 8, 2019 2:39 PM
Did you get a resolution to this issue?
Saturday, March 9, 2019 8:27 AM | 3 votes
Hi, try this:
-- Step1: connect to source server master database using Server Admin credentials
CREATE LOGIN copyUser WITH PASSWORD = '{PutStrongPassword}';
CREATE USER copyUser FROM LOGIN copyUser;
-- Step2: connect to source database using Server Admin credentials
CREATE USER copyUser FROM LOGIN copyUser;
ALTER ROLE db_owner ADD MEMBER copyUser;
-- Step3: Connect to destination server master database using Server Admin credentials
CREATE LOGIN copyUser WITH PASSWORD = '{PutStrongPassword}';
CREATE USER copyUser FROM LOGIN copyUser;
ALTER ROLE dbmanager ADD MEMBER copyUser;
-- Step4: Connect to destination server master database using copyUser
CREATE DATABASE testcopy AS COPY OF mysource.mydb1 (EDITION = 'Standard', SERVICE_OBJECTIVE = 'S1' );
If you found this post useful, Please "Mark as Answer" or "Vote as Helpful", this will keep us motivated to help in the forums, best regards.