3

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?

6
  • You could potentially get the answer here: stackoverflow.com/questions/31120912/… Commented Aug 31, 2016 at 22:01
  • @MartinParkin Unfortunately that query doesn't seem to help much. I don't see anything in the list of permissions that deals with creating databases Commented Aug 31, 2016 at 22:12
  • You're trying to query the Azure permissions through T-SQL? Commented Sep 1, 2016 at 17:45
  • @drediske That's correct Commented Sep 1, 2016 at 18:50
  • Alright. I'll frame my answer that way once I'm out of meetings. Commented Sep 1, 2016 at 19:03

2 Answers 2

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
Sign up to request clarification or add additional context in comments.

Comments

0

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;

1 Comment

Didn't return anything for me. Is there something I'm missing maybe? I can create databases no problem, so presumably I would have that role?

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.