One of the pre-requesting of Comprehensive Database Performance Health Check is to have access to the database which we are going to tune. You may find it surprising that quite a few times, I ended up in a situation where I have a client who wants me to help with their database performance but they themselves also do not have access to the server and database. In those scenarios, we end up on a very elaborated training session. Let us learn today about how to List Database Available for Current User.
Nowadays whenever I work with the client on performance tuning engagement, I usually send them two scripts.
- List Users with System Admin (sysadmin) Rights (Solution 1, Solution 2)
- List Database Available for Current User (In this blog)
Let us see a script which will list all the database the current logged in user has access to.
SELECT name, HAS_DBACCESS(name) HasAccess FROM sys.databases
The query above will give results something similar to the following image.
In the query, we have used the function HAS_DBACCESS. I have not seen much of the usage of this function in the real world but honestly, it is a very efficient function to figure out the access to the current database for the logged-in user.
Here is what we should remember about the result of the function HAS_DBACCESS. 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 is not valid. When this function returns 0 that it also means that the database is offline, in suspect mode, single-user mode, or other issues that are preventing us to use the database.
Well, that’s it for today. You may run the above script and see how many databases you have access to.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
