List Databases Available for Current User -SQL Server

Introduction

Sometimes, we may be curious to know to which databases do we have access or we need to access a particular database but not sure that we have access or not. In this article, we will discuss a T-SQL that will list the Databases available for current user.

For Particular Database

HAS_DBACCESS  – This SQL function, is an efficient function to return check the current logged in user has access rights to particular database. This functions return type is INT.

Example

I have a database as MachineLearningServices, but I am not sure that do I have access or not. By using HAS_DBACCESS  function, I am going to check it,

SELECT HAS_DBACCESS('MachineLearningServices') AS CanAccess

By using the above T-SQL we can able to know that do we have access to a particular Database or not.

If the T-SQL returns as 1 (One), then the current logged-in user has access to the database. If the result value is 0 (Zero), then the current logged-in user has no access to database and if it returns NULL, then the database name is not valid.

For List of Databases

Now. let’s have a look T-SQL to list all the databases to show the current logged-in user has access or not. To get the list of database names, we need to access the table sys.databases.

Example

SELECT 
		NAME AS DatabaseName, 
		HAS_DBACCESS(name) CanAccess
FROM sys.databases

When we run the above T-SQL, we will get result list as,

Conclusion

In this article, we discussed the possible ways to check Databases available for the current logged-in users. I hope you all found this article useful. Please share your feedback in the comment section and kindly share this article.