HAS_DBACCESS Function: List the Databases You Can Open

The HAS_DBACCESS function tells you whether the current login can open a database. One query over sys.databases turns it into a list of the databases you can use. Every zero in that list has a reason.

Why Check Access First

Before any tuning work, the first question is which databases the login can open. It sounds too small to ask. Yet a login that cannot open a database cannot tune it, test it or read its plans.

The HAS_DBACCESS function returns 1 when the login has access and 0 when it has none. It returns NULL when the name is not a database. The call is HAS_DBACCESS(‘name’). The query below uses it with sys.databases to cover every database in one pass.

Build Six Databases to Test

The demo creates six small databases and a throwaway login named AccessCheckLogin. The login is disabled the moment it is created, and the demo never signs in with it. The password is a placeholder, and the cleanup removes the login. The login gets a user in every database except one. Then four databases change state. One goes offline, one becomes single user, one becomes read only and one becomes restricted user. The other two stay as they are.

USE master;
GO
IF SUSER_ID(N'AccessCheckLogin') IS NULL CREATE LOGIN AccessCheckLogin WITH PASSWORD = N'ReplaceWithAStrongPassword1!', CHECK_POLICY = OFF;
ALTER LOGIN AccessCheckLogin DISABLE;
IF DB_ID(N'AccessCheckOpen') IS NULL CREATE DATABASE AccessCheckOpen;
IF DB_ID(N'AccessCheckNoUser') IS NULL CREATE DATABASE AccessCheckNoUser;
IF DB_ID(N'AccessCheckClosed') IS NULL CREATE DATABASE AccessCheckClosed;
IF DB_ID(N'AccessCheckSingle') IS NULL CREATE DATABASE AccessCheckSingle;
IF DB_ID(N'AccessCheckReadOnly') IS NULL CREATE DATABASE AccessCheckReadOnly;
IF DB_ID(N'AccessCheckRestricted') IS NULL CREATE DATABASE AccessCheckRestricted;
GO
EXEC AccessCheckOpen.sys.sp_executesql N'CREATE USER AccessCheckUser FOR LOGIN AccessCheckLogin;';
EXEC AccessCheckClosed.sys.sp_executesql N'CREATE USER AccessCheckUser FOR LOGIN AccessCheckLogin;';
EXEC AccessCheckSingle.sys.sp_executesql N'CREATE USER AccessCheckUser FOR LOGIN AccessCheckLogin;';
EXEC AccessCheckReadOnly.sys.sp_executesql N'CREATE USER AccessCheckUser FOR LOGIN AccessCheckLogin;';
EXEC AccessCheckRestricted.sys.sp_executesql N'CREATE USER AccessCheckUser FOR LOGIN AccessCheckLogin;';
GO
ALTER DATABASE AccessCheckClosed SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AccessCheckSingle SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AccessCheckReadOnly SET READ_ONLY WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AccessCheckRestricted SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

List the Databases the Login Can Open

The next query runs as the throwaway login. EXECUTE AS LOGIN needs the IMPERSONATE permission, which a sysadmin has. The last column turns each 0 into a short reason. A database that is not online comes first, because nobody can open it. Then the access mode, and then the missing user.

EXECUTE AS LOGIN = N'AccessCheckLogin';
SELECT d.name AS DatabaseName, d.state_desc AS State, d.user_access_desc AS AccessMode, HAS_DBACCESS(d.name) AS HasAccess,
       CASE WHEN HAS_DBACCESS(d.name) = 1 THEN N'Can open'
            WHEN d.state_desc <> N'ONLINE' THEN N'Not online'
            WHEN d.user_access_desc <> N'MULTI_USER' THEN N'Access mode'
            ELSE N'No user for this login'
       END AS Reason
FROM sys.databases AS d
WHERE d.name LIKE N'AccessCheck%'
ORDER BY d.name;
REVERT;

The login sees every row of sys.databases, because the public role holds VIEW ANY DATABASE by default. Seeing a database is not the same as opening it. That is the point of the last two columns.

What Each Zero Means

The HAS_DBACCESS function answers 0 for four different reasons. The table below comes from the demo. The sysadmin column comes from the same query without EXECUTE AS. The single user case needs a second window. Connect to AccessCheckSingle there and leave the window open. Then run the function again from the first window. A database in the SUSPECT state also returns 0, but the demo does not build one.

Three of those rows surprise people. A sysadmin gets 1 for a database that has no user for them. A sysadmin is dbo in every database. A single user database answers 0 only while another session holds the one slot. A restricted database answers 0 for an ordinary login, because only members of db_owner, dbcreator and sysadmin can connect.

The guest user is the quiet exception. GRANT CONNECT TO guest in AccessCheckNoUser turned the 0 into a 1 for the login. REVOKE CONNECT FROM guest turned it back. Check guest on the databases where the answer looks too generous.

Loop Over the Usable Databases Only

A maintenance script that visits every database fails on the first one it cannot open. Filter the list first. The query below keeps the databases that are online and open to the login. Run as the throwaway login, it returns three of the six demo databases.

EXECUTE AS LOGIN = N'AccessCheckLogin';
SELECT d.name AS DatabaseName
FROM sys.databases AS d
WHERE d.name LIKE N'AccessCheck%' AND d.state_desc = N'ONLINE' AND HAS_DBACCESS(d.name) = 1
ORDER BY d.name;
REVERT;

Drop the LIKE filter to run it against a real server. The result changes whenever a database goes offline, so build the list at run time and not once.

Run It as the Login You Care About

A report that a sysadmin runs through the HAS_DBACCESS function says every online database is fine. The login of the application can see a different list. Use EXECUTE AS LOGIN for the login you are checking, as the demo does. Or connect as that login and run the plain query.

Is HAS_DBACCESS Enough?

You could argue that a plain USE statement is the real test. It is, but running USE against forty databases is slow and noisy, and one error stops the script. The function checks them all in one pass. It checks the right to connect, not permissions on tables. A login can pass the check and still fail on a SELECT.

What to Remember

Run the HAS_DBACCESS function as the login you care about. Read each zero against the state, the access mode and the user list. A 1 means the login can connect, nothing more.

When you finish the demo, remove the databases and the login. Bring the closed database online first. A drop while it is offline leaves its files on disk. The next CREATE DATABASE with that name then fails with Msg 5170, because the file already exists. Close the second window first. A drop fails with Msg 3702 while a session still uses AccessCheckSingle. The cleanup below uses the right order.

USE master;
GO
IF DB_ID(N'AccessCheckClosed') IS NOT NULL ALTER DATABASE AccessCheckClosed SET ONLINE;
DROP DATABASE IF EXISTS AccessCheckOpen, AccessCheckNoUser, AccessCheckClosed, AccessCheckSingle, AccessCheckReadOnly, AccessCheckRestricted;
IF SUSER_ID(N'AccessCheckLogin') IS NOT NULL DROP LOGIN AccessCheckLogin;

A 1 from HAS_DBACCESS is not permission to work, it is permission to walk in.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.