Server-Level Roles on Azure SQL Database

Question

Hello to all.

I am a computer science student and I am currently learning SQL Server. I currently have a free student subscription which gives me access to install a SQL database on Azure.

In order to affiliate myself with the environment, I decided to create two logins and two users (affiliated with these logins) on which I would like to be able to assign them different server-roles, but I am running into a problem.

On this page: server-level-roles, I noticed that we can assign different roles. The one I am interested in is dbcreator (example I am trying to document).

However, when I try to assign dbcreator to my (fictitious) stephanlogin login ;

ALTER SERVER ROLE dbcreator ADD MEMBER stephanlogin;  
ALTER SERVER ROLE dbcreator ADD MEMBER stephanuser;  
EXEC sp_addrolemember 'dbcreator', 'stephanlogin';  
EXEC sp_addrolemember 'dbcreator', 'stephanuser';  

I get different errors;

Msg 15151, Level 16, State 1, Line 1  
Cannot alter the server role 'dbcreator', because it does not exist or you do not have permission.  
Msg 15151, Level 16, State 1, Line 2  
Cannot alter the server role 'dbcreator', because it does not exist or you do not have permission.  
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 35 [Batch Start Line 0]  
User or role 'stephanlogin' does not exist in this database.  
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 35 [Batch Start Line 0]  
User or role 'stephanuser' does not exist in this database.  

My questions are simple :

• How do I assign a role-server on one of my logins (or user)?
• Is there an error in my commands?
• Or, since I'm using Azure SQL Database as a PaaS, I don't have permission to add such server roles to a login? (If so, do you have any Microsoft documentation that states this please).

Thank you for your attention to my request and have a nice day

Answer 1

Hello @HE-ARC User Welcome to Microsoft Q&A and Thank you for posting your question.
@Alberto Morillo and @Guoxiong Thanks also for sharing your insights and for helping the community.
The script above only applies to SQL Server and Parallel Data Warehouse Azure SQL does not support Server Roles , Please follow this documentation for more info.
Please do let us know if you have further query. Thanks
.

Answer 2

If you want to assign a server role to a login, you need to use the system stored procedure sp_addsrvrolemember. For your case, try this:

EXEC sp_addsrvrolemember 'stephanlogin', 'dbcreator';  

Answer 3

The proper role name on Azure SQL database is not dbcreator but dbmanager. Logins assigned to this role are able to create and drop databases.

To assign a login to this role, please see below example usign an Azure Active Directory user..

-- add contained Azure AD user   
CREATE USER [******@sqlcoffee.com]   
FROM EXTERNAL PROVIDER   
WITH DEFAULT_SCHEMA = dbo;    
    
-- add user to role(s) in db   
ALTER ROLE dbmanager ADD MEMBER [******@sqlcoffee.com];   

For a normal SQL login you can assign the login to the role as shown below:

alter role [dbmanager] add member sqlloginname;  

You can learn more about the dbmanager role here.

Answer 4

Hello to all.

Thank you for taking the time to write to me.

I have indeed managed to "solve" my problem. That is to say, I have assigned the dbmanager role to my stephanlogin login.

Here are the queries I used in my SQL database server (and it has worked) :

CREATE LOGIN michaellogin WITH password='...';

CREATE USER michaeluser FOR LOGIN michaellogin
WITH DEFAULT_SCHEMA = master;

alter role [dbmanager] add member michaeluser;

I wish you a good day