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.
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.
SELECT NAME AS DatabaseName, HAS_DBACCESS(name) CanAccess FROM sys.databases
When we run the above T-SQL, we will get result list as,
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.