How to check if user has create database access in SQL Azure?

Question

How do I check to see if the current user has access to create a database in SQL Azure?

In regular SQL Server, I have various easy methods at my disposal to check this. Normally, I might use IS_SRVROLEMEMBER('dbcreator'). Apparently SQL Azure has a dbmanager role instead, but IS_SRVROLEMEMBER('dbmanager') does not work either.

I also tried HAS_PERMS_BY_NAME(null, null, 'CREATE ANY DATABASE') but that does not work either.

I've verified that I do indeed have access to create a database. Does anyone have suggestions?

Answer 1

First: the query that should get you as close as possible to what you're looking for on the server specifically:

SELECT sys.database_role_members.role_principal_id, role.name AS RoleName,   
sys.database_role_members.member_principal_id, member.name AS MemberName  
    FROM sys.database_role_members  
JOIN sys.database_principals AS role  
     ON sys.database_role_members.role_principal_id = role.principal_id  
JOIN sys.database_principals AS member  
     ON sys.database_role_members.member_principal_id = member.principal_id
WHERE role.name = 'db_owner';  

From the RBAC poster on SQL Server:

NOTE: CREATE DATABASE is a database level permission that can only be granted in the master database. For SQL Database use the dbmanager role

From the CREATE DATABASE (Azure SQL Database) documentation

To create a database a login must be one of the following:

The server-level principal login

The Azure AD administrator for the local Azure SQL Server

A login that is a member of the dbmanager database role

You can edit db_owner to any role on this page - this is the azure specific role:

dbmanager Can create and delete databases. A member of the dbmanager role that creates a database, becomes the owner of that databasee which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the dbmanager role do not necessarily have permission to access databases that they do not own.

An important note: You may have intertwined two different Role Based Access Control levels. SQL RBAC and Azure RBAC both have some relationship to SQL Server/SQL DB. In that way, they are related, certainly, but are not the same thing. For example: Being able to create a DB via the portal is different than being able to create a DB while connected to the server; it is possible to give an Azure user the ability to create a database while that user has no valid login to connect to the server. (Which would not be true if Azure RBAC and SQL RBAC were identical.)

Users with SQL DB Contributor or SQL Server Contributor roles will be able to create a database without ever connecting to the database. I examined enumerating these roles in a partially related question here.

You'll be able to audit the RBAC of Azure most easily through this powershell command:

Get-AzureRmRoleAssignment -ResourceGroupName <your resource group name> 
-ResourceType Microsoft.Sql/servers -ResourceName <your server name> 
-IncludeClassicAdministrators

Answer 2

I believe this should do what you are looking for. It will return "1" if the current login has the dbmanager role. You need to execute this in the master database.

SELECT 1 AS DatabaseManager
FROM sys.database_role_members rm 
JOIN sys.database_principals r 
    ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m 
    ON rm.member_principal_id = m.principal_id
WHERE r.name = 'dbmanager' and m.name = CURRENT_USER;