All we need is an easy explanation of the problem, so here it is.
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂