How to detect the "Server admin" user on Azure SQL Server

Question

The following article describes the AZURE "Server admin" user. https://learn.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

This user has access to every database but unfortunately the HAS_DBACCESS(%database_name%) function fails to return 1 so I need some other way to detect this user and recognize that they have access to all databases.

I tried a number of the other methods including:

• IS_MEMBER ('db_owner')
• IS_SRVROLEMEMBER ('serveradmin')
• IS_SRVROLEMEMBER ('sysadmin')

Note: Oddly enough the HAS_DBACCESS(%database_name%) actually returns NULL.

Any suggestions would be appreciate.

The below queries were run from the Master database while logged in as rootadmin, which is the name of my Server Admin user.

Edit: When I switched to using the "Demo" database the query results changed and HAS_DBACCESS(t.name) returned NULL for the Master database and "1" for the "Demo" database.

So now I am confused.

Answer 1

With Microsoft SQL Azure Databases you're user won't be a member in any server role and you won't have access to that information. So, IS_SRVROLEMEMBER will, in fact, return zeros. You only have access at the database level and not the server level--unless you build and use a SQL VM instead.

From the Microsoft documentation you can use the following to test this:

IF IS_SRVROLEMEMBER ('sysadmin') = 1  
print 'Current user''s login is a member of the sysadmin role'  
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0  
print 'Current user''s login is NOT a member of the sysadmin role'  
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL  
print 'ERROR: The server role specified is not valid.';  

However, your user will have access to the database level roles. From Microsoft documentation you can test this example:

IF IS_ROLEMEMBER ('db_datareader') = 1  
print 'Current user is a member of the db_datareader role'  
ELSE IF IS_ROLEMEMBER ('db_datareader') = 0  
print 'Current user is NOT a member of the db_datareader role'  
ELSE IF IS_ROLEMEMBER ('db_datareader') IS NULL  
print 'ERROR: The database role specified is not valid.';  

There is a caveat though, you need to be in the context of your personal database and not the master database (my database is called TESTING):

Once you're in the correct database context, the following functions, and the like, work as expected:

select HAS_DBACCESS('testing')

select IS_MEMBER('db_owner')

Note:

select HAS_DBACCESS('db_name') will return a NULL value because there's no database called 'db_name'. You can change this to the db_name() function. For example,

select HAS_DBACCESS(db_name()) 

or, hard code the database name--TESTING in my case.

select HAS_DBACCESS('TESTING')