Error trying to add a login to Azure SQL Special Role 'dbmanager'

Question

Upon running these commands on an Azure SQL instance:

CREATE LOGIN test WITH PASSWORD=N'Passw0rd'
GO

ALTER ROLE dbmanager ADD MEMBER test
GO

... I'm getting an error:

Cannot add the server principal 'test', because it does not exist or you do not have permission.

I've also tried this:

ALTER SERVER ROLE dbmanager ADD MEMBER test
GO

I've verified that the login exists. I'm logged in as the server's sysadmin.

Apparently I was able to get this to work a year ago: How to add a login to the dbcreator role? But that was then and this is now. It ain't workin'.

The base issue, however, remains the same: I need to be able to create a database under the scope of a given login and then have that login be able to manipulate the new database (to include DDL), all without manual intervention via db scripts. (This is for use with Entity Framework Code First Migrations, which can be configured to automatically create the database named in the connection string if it doesn't already exist.)

This capability exists in SQL Server (by adding a login to the dbcreator role), but Azure SQL is proving a tougher nut to crack.

Also: I've noticed a conflict in the documentation for Special Roles, and I've started an issue on the matter. I don't know if it's related to my error, but I thought it worth mentioning.

How can I enable a login to create new databases in Azure SQL, in a way that the login is automatically added to the new database as a user with read/write/ddl permissions?

Answer 1

The dbmanager role is a database role not a server role. That database role only exists in the master database.

I.e., you have to create a user in the master database for the login in question and add that user to the dbmanager role. As seen below:

CREATE LOGIN test WITH PASSWORD=N'Passw0rd'
GO

--Fails:
ALTER ROLE dbmanager ADD MEMBER test
GO

CREATE USER testUsr FOR LOGIN test
GO

--Executes successfully:
ALTER ROLE dbmanager ADD MEMBER testUsr
GO