Geek asks and answers

What permissions are required to copy databases in SQL Azure

I wrote a small application for backing up Azure SQL databases using the very useful CREATE AS COPY OF command in SQL Azure. eg.

CREATE DATABASE MyNewDB AS COPY OF MyOldDB

I run this command with the administrator name (the first input you get when creating the server). My question is: what are the minimum permissions for a new login to execute the above command?

So far I have done:

-- IN MASTER DB --
CREATE LOGIN DBCreator WITH PASSWORD = '?????????????'
CREATE USER DBCreator FROM LOGIN DBCreator;
EXEC sp_addrolemember 'dbmanager', 'DBCreator';

-- IN MyOldDB --
-- (I ran this stuff when the previous commands didn't do it) --
CREATE USER DBCreator FROM LOGIN DBCreator;
EXEC sp_addrolemember 'db_datareader', 'DBCreator';  --

And the result when executing the above CREATE DATABASE command:

CREATE DATABASE permission denied in database 'MyOldDB'.
+5
source share
1 answer

I could not add the db_owner role to the new login command

EXEC sp_addrolemember 'db_owner', 'NewLogin'

gives an error message

Cannot alter the role 'db_owner', because it does not exist or you do not have permission.

But using the dbmanager role works

EXEC sp_addrolemember 'dbmanager', 'NewLogin'

SQL Azure SQL Database.

+1

More articles:


All Articles