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.
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?