Find databases list where user has access in SQL Server

Recently our client wanted to know if there's a way for him to figure out which additional databases his user has access to. When we discovered there was an SQL function that can help with just such a situation, we were pleasantly surprised. This is what SQL Guru means because on the surface it looks like a common phrase or word but once you uncover its true meaning, it’s powerful!

We can use HAS_DBACCESS() function to check the database list where user has the access.

• The HAS_DBACCESS ( ) function returns 1 if the user has access to the database, 0 if they have no access to the database, and NULL if it was called with a non-existent database name.

• It returns 0 if the database is offline, suspect or in single-user mode.

For example, here's what this function does for views and users that have access rights to several databases:

SELECT name AS DB,
HAS_DBACCESS(name) AS UserHasDBAccess
FROM sys.databases
WHERE HAS_DBACCESS(name) = 1

As per the screenshot, the user has access on system databases (Master, Model, MSDB, TempDB) and user database [ABCD]

To test the function HAS_DBACCESS() , let's create a new user and give access only for the [ABCD] database.

The following script creates a new login in master database and user with db_datareader access in the database [ABCD]

USE [master]
GO
CREATE LOGIN [DemoLogin] WITH PASSWORD=N'India@123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [ABCD]
GO
CREATE USER [DemoLogin] FOR LOGIN [DemoLogin]
GO
USE [ABCD]
GO
ALTER ROLE [db_datareader] ADD MEMBER [DemoLogin]
GO

Let's rerun the SQL Script under the security context of user 'DemoLogin'. It returns the output as below.

Note: The users can create, drop objects in the TempDB database.