Resolving CREATE DATABASE permission denied in database [db] when running Create Database [BKUP] as copy of [DB] in SQL Azure

  • Article

The easiest way to back up databases in SQL Azure is to run Create Database [BKUP] as copy of [DB] command, but this often fails with error

CREATE DATABASE permission denied in database 'DB'

What this error really means is that you do not have permissions to perform this operation, this is how to resolve the problem

1- Find the user with the right set of permissions by running the following command - and chosing the user associated with the database you are trying to copy

select d.name, l.name from sys.sysdatabases as d
left join sys.sql_logins as l
on d.sid = l.sid

2- Connect as this specific user and try to run the command, it should work

3- In some rare cases, the user gets deleted by mistake, so you see the user as NULL from the previous query, in this case you can create a new user with the same SID

Run the following query in master:

SELECT [name], [sid]
FROM [sys].[sql_logins]
WHERE [type_desc] = 'SQL_Login'

Run the following query in the database [db]:

SELECT [name], [sid]
FROM
[sys].[database_principals]
WHERE [type_desc] = 'SQL_USER'

4- Get the user SID from the previous query, now you can create a new user using the same SID
CREATE LOGIN [<login>]
WITH PASSWORD = ‘<password’, SID =0x010600000000006480000000000002A83C9BDF82B8E22220AC07FA6A02474304 <-- This is the SID you got from the previous step

5- Now you can connect as this user and run your Copy command

You can monitor the progress using this query
select name, state,state_desc from sys.databases where name = 'BKUP'