Azure SQL Server Security

Question

So I have become our supposed Azure SQL Server DBA...shaking my head here...

I know Microsoft Azure SQL behaves differently than your garden variety VM SQL Server.

That being said, since I am a novice and just getting my feet wet here, can someone point me the right direction where I can find T-SQL queries that will show Login Permissions and Role Permissions or even what steps I need to invoke within the Azure Portal to somehow determine SQL Server Login and Role permissions.

Thanks for your review and am hopeful for a reply.

Answer 1

Hi @Bobby P ,

Thanks for using Microsoft Q&A !!
You can use the below query to get login and role permissions for your azure SQL database users

SELECT DP1.name AS DatabaseRoleName,     
   isnull (DP2.name, 'No members') AS DatabaseUserName     
 FROM sys.database_role_members AS DRM    
 RIGHT OUTER JOIN sys.database_principals AS DP1    
   ON DRM.role_principal_id = DP1.principal_id    
 LEFT OUTER JOIN sys.database_principals AS DP2    
   ON DRM.member_principal_id = DP2.principal_id    
WHERE DP1.type = 'R'  
ORDER BY DP1.name;  

You can refer sys.database_role_members for additional details.
Please let me know if you have any other question

Thanks
Saurabh

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Answer 2

First thing to remember is that Azure SQL Database are "contained" databases - so you don't normally define a login to access it but normally you create a USER directly in the database.

There can still be some cases where you need a LOGIN created in the master database and allow this to have access across many databases that are created on that logical server. However, when you look at the master database in Azure, it does not have the same security roles as SQL Server in a VM does - it only has loginmanager (similar to securityadmin role) and dbmanager (similar to dbcreator role). These exist only in the master database. Also, although the other fixed roles will appear in queries against the master database, since you cannot create any database objects in master in Azure SQL Database then these are effectively irrelevant and redundant.

In each user database, you will have the same internal fixed database roles as for a VM based instance.

Server Role Permissions

As this is effectively limited to the loginmanager and dbmanager roles and these are fixed and restricted and pretty much well defined, there isn't much you need to be able to query around these other than membership.

To see who is a member you first need to connect in the context of the master database (remember that each connection to Azure SQL Database is to a specific database, not just to an instance like a VM based SQL Server).

The system views
sys.server_role_members
and
sys.server_principals
are not valid for Azure SQL Database and so you will need to instead look at
sys.database_role_members
and
sys.database_principals
in the context of the master database.

Once connected to master then running

SELECT DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, N'No members') AS DatabaseUserName  
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.name IN (N'dbmanager', N'loginmanager')
ORDER BY DP1.name;

will show you if any LOGINS have been added to these Roles.

Database Role Permissions

This should essentially be exactly as you would do for a VM based SQL Server instance. As above however, note that you probably only need to be working from
sys.database_principals
and not need to link this back to LOGINS defined in master for most cases.

Once connected to each individual database you can use the same system views as a VM based SQL instance to check permissions:

SELECT * FROM sys.database_role_members
SELECT * FROM sys.database_principals
SELECT * FROM sys.database_permissions

Hope that makes it a bit clearer?