List the available Databases for Current User in SQL SERVER

Introduction :  

One of the pre-needful of Database Performance Health Check is to have access to the database which we’re going to tune. As SQL DBAs we can also additionally discover it unexpected that quite sometimes, we ended up in a scenario in which we’ve got a customer who needs us to assist with their database overall performance however they themselves additionally do now no longer have access to the server and database. 

Let us see a script that could list all the databases, the present logged-in user has access to –

SELECT Name, HAS_DBACCESS(Name) AS HasAcces FROM sys.databases

The above query will give results something similar to the below  –

Name	HasAcces
master	1
tempdb	1
model	1
msdb	1
geekdb	1

In the query, we have used the function HAS_DBACCESS which results in information about whether the user has access to the all database. It 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 isn’t valid.

Query to check that do we have access to a specific database or not –

SELECT HAS_DBACCESS('databasename') AS HasAccess

Example-1 :

SELECT HAS_DBACCESS('geekdb') AS HasAccess

HasAccess
1

As a SQL DBA, we should always remember the results of the function HAS_DBACCESS. When this function results in 0, it might also mean that the database is offline\suspect mode\single-user mode, or other issues that are preventing us to use the database.

Example-2 :

To check the current user has access to the below databases –

Run above query in SQL Server Management Studio –

Let us check where the current user has access to “SQL_DBA1” –

SELECT HAS_DBACCESS('SQL_DBA1') AS HasAccess

The result was NULL as the database does not exist in the current instance.