WikiDBA

Sometimes it’s happened, a developer try to access a database but he/she could not get succeed and after few R&D he/she came to know that he/she has no access right for that particular DB. For same, a very useful SQL Server’s function HAS_DBACCESS can be used to get the list of all Databases having access details of currently logged user. Here is a simple script to get the details as

Select Name, case HAS_DBACCESS(name) when 0 then ‘No Access’ else ‘Access’end AS DB_Access_Status from sys.databases

Note : if any Database is in RECOVERY Mode, it will shows as NO Access for that DB.