0

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.

enter image description here

1 Answer 1

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):

enter image description here

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') 
4
  • Thank you very much for the detailed answer, what you said seems to all check out. I will dig into it some more tomorrow. Note: You can see from the query that I was calling HAS_DBACCESS() correctly with the name of the db, I was using the string 'db_name' as an example.
    – AnthonyVO
    Oct 12, 2017 at 4:24
  • Based on your answer I now call HAS_DBACCESS() on the initial database list looking for a 0 or 1. If I get a NULL, I then open a connection just for that database and call HAS_DBACCESS() again to get the true value. That seems to work for me. Thanks.
    – AnthonyVO
    Oct 13, 2017 at 16:11
  • The Server admin and Azure AD admin accounts has the following characteristics: *These accounts are not members of the standard SQL Server sysadmin fixed server role, which is not available in SQL database. Running the first query indicates that indeed, the server admin designated for the SQL Server and listed in the overview on the Azure portal is NOT a member of the sysadmin role. I don't see how this is useful.
    – Triynko
    Feb 12, 2018 at 19:00
  • @Triynko, Never saw your comment. My application is required to work in all variants of SQL Server, not just Azure. The first HAS_DBACCESS() covers non-Azure installs.
    – AnthonyVO
    Nov 1, 2021 at 22:52

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.