SQL Server has a HAS_DBACCESS() function that returns information about whether the user has access to a specified database.

Syntax

The syntax goes like this:

HAS_DBACCESS ( 'database_name' )

The function returns 1 if the user has access to the database, 0 if the user has no access to the database, and NULL if the database name is not valid.

It returns 0 if the database is offline or suspect, and it returns 0 if the database is in single-user mode and the database is in use by another user.

Example

Here’s an example to demonstrate:

SELECT HAS_DBACCESS('KrankyKranes');

Result:

1

In this case, 1 was returned, which means that the user has access to the KrankyKranes database.

Non Existent Database

If the database doesn’t exist, the result is NULL:

SELECT HAS_DBACCESS('Oops');

Result:

NULL

Check All Databases

We can use the following query to check access to all databases in the SQL Server instance:

SELECT 
    name AS DB,
    HAS_DBACCESS(name) AS HasDBAccess
FROM sys.databases;

Result:

+-----------------------+---------------+
| DB                    | HasDBAccess   |
|-----------------------+---------------|
| master                | 1             |
| tempdb                | 1             |
| model                 | 1             |
| msdb                  | 1             |
| Music                 | 1             |
| KrankyKranes          | 1             |
| Test                  | 1             |
| WideWorldImporters    | 1             |
| World                 | 1             |
| DomainDispute         | 1             |
| PetHotel              | 1             |
| StereoSystems         | 1             |
| NarrowNationExporters | 1             |
+-----------------------+---------------+

In that case, I had access to all databases.

Here’s what happens when I run the query as a user with access to less databases:

SELECT 
    name AS DB,
    HAS_DBACCESS(name) AS HasDBAccess
FROM sys.databases;

Result:

+-----------------------+---------------+
| DB                    | HasDBAccess   |
|-----------------------+---------------|
| master                | 1             |
| tempdb                | 1             |
| model                 | 0             |
| msdb                  | 1             |
| Music                 | 0             |
| KrankyKranes          | 0             |
| Test                  | 1             |
| WideWorldImporters    | 0             |
| World                 | 0             |
| DomainDispute         | 0             |
| PetHotel              | 0             |
| StereoSystems         | 0             |
| NarrowNationExporters | 0             |
+-----------------------+---------------+