0

Using SSMS, I'm having trouble adding a new login to the server dbcreator role on my Azure SQL instance.

Here's the syntax I'm using:

CREATE LOGIN test WITH PASSWORD = 'SuperSecret!'
ALTER SERVER ROLE [dbcreator] ADD MEMBER [test]

This works fine on a local SQL instance, but in Azure it fails with

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

I've verified that I'm connected to the server using the Server Admin Login credentials, but the problem persists. Internet searches only turn up the above T-SQL code.

How can I add a login to the dbcreator role in Azure SQL?

1

The dbcreator role doesn't exist in an Azure SQL Database because there are no server level roles in the Azure SQL Database product.

You can find which types of Microsoft database products do support it on the Server-Level Roles documentation. It appears to just be SQL Server and Parallel Data Warehouse.

For further information on provisioning administrative capabilities in Azure SQL Database, please see Create additional logins and users having administrative permissions.

Additionally, it does seem despite there not being traditional server level roles in Azure SQL Database, there are two dedicated roles that exist in the master database only, to mimic similar behavior. dbmanager is one of them, and is used to provision CREATE DATABASE permissions to a User. Please see the docs on this in Special Roles for SQL Database and Azure Synapse. This may be what you're looking for, as you won't have to provide sysadmin privileges then.

5
  • Hm, this could be problematic. I want this login to be able to create databases, but I don't want it to be a sysadmin. Will this be possible?
    – InteXX
    Jul 15 '21 at 0:49
  • In the docs you linked (thank you), this statement seems self-contradictory for the first SQL Database step: "Create an additional SQL login in the master database." Aren't logins by definition at the server level, not at the database level?
    – InteXX
    Jul 15 '21 at 0:56
  • 1
    @InteXX Please see my last update to my answer, as I think I've found a way that might be what you're looking for. Regarding your second question, I'm unsure if that is a typo by Microsoft, or is accurate in those special cases, but neither apply to Azure SQL Database anyway.
    – J.D.
    Jul 15 '21 at 1:03
  • My testing results in SSMS just now are somewhat disappointing. I was able to add the database under the new user, after issuing ALTER ROLE dbmanager ADD MEMBER test. So that part works. Unfortunately, however, at least for my case (I'm using Entity Framework Code First Migrations), the login isn't added to the new database as the dbo user, as it is under on-prem MSSQL. In fact, no user is added to the new database at all. Consequently, when using Migrations under Azure SQL, the new database is created but the login can't access anything within it.
    – InteXX
    Jul 15 '21 at 2:43
  • So this means we have two options: 1) Use the sysadmin login for normal read/write operations, or 2) Create the database and configure security manually using SSMS. Option #2 seems the more sane approach. Fortunately, in my case at least, my web app architecture doesn't call for databases to be created on the fly. Migrations will detect that the database already exists and skip to the schema updates. It certainly won't be the end of the world.
    – InteXX
    Jul 15 '21 at 2:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.