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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
10
  • 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, 2021 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, 2021 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, 2021 at 1:03
  • 1
    Oh man, I created a hot mess with this one. First, I forgot to update you with the results. Then I got it working, but not before I falsely accused the documentation team of publishing conflicting rules. In the end, this solution worked. I can now create a new database with the authenticated user as dbo. Which is what I've been after all this time. Finally. Ugh.
    – InteXX
    Apr 22, 2022 at 12:24
  • 1
    Everybody kept saying user, and for some reason all I could see was login. It's my parents' fault. They gave me mental blocks for Christmas.
    – InteXX
    Apr 22, 2022 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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