Updated: Dec 31, 2021
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.