List the available Databases for Current User in SQL SERVER
Last Updated :
27 Apr, 2021
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
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.
Similar Reads
List All Databases in SQL Server In SQL Server, databases are crucial for storing and managing data efficiently. Whether we are managing a large enterprise system or a small application, understanding how to list all the databases on our SQL Server is essential. In this article, we will write SQL queries that help us to retrieve al
3 min read
MySQL | DATABASE() and CURRENT_USER() Functions In MySQL, certain functions provide crucial information about the current session which can be particularly useful when working with multiple databases or managing user permissions. Two such important functions are DATABASE() and CURRENT_USER().In this article, We will learn about the MySQL DATABASE
3 min read
Create Database in MS SQL Server Databases in Microsoft SQL Server are crucial for managing data, categorized into system databases, which are auto-created and user databases, created by users. In this article, We will learn about the basics of system and user databases along with methods for creating and managing them using T-SQL
5 min read
SQL Server Query to Find All Permissions/Access for All Users in a Database In SQL databases, managing and auditing user permissions is crucial for ensuring security and proper access control. To find out what permissions or access levels are granted to users within a database, we can use built-in functions such as sys.fn_my_permissions and sys.fn_builtin_permissions. These
3 min read
Check whether a Table exists in SQL Server database or not Before creating a table, it is always advisable to check whether the table exists in the SQL Server database or not. Checking for table existence before creation helps in avoiding duplication errors, ensures data integrity, and enables efficient database management. There are multiple methods in SQL
3 min read