sqlsql-serversql-server-2008sql-server-2008-r2

How to find databases which accessible to me in Sql server?

I want to write a query which will give all the databases on the server which accessibility to the user (read or more access).

Solution

  • You can filter using HAS_DBACCESS:

    SELECT name 
  FROM sys.databases
 WHERE HAS_DBACCESS(name) = 1;

    If you need additional filters, like excluding system databases (which unfortunately there is no direct column to signify) or certain known and probable system databases, like those involved with SSIS or replication (leave these out if you don't use these features because, if not, someone could have named a user database the same):

       AND database_id > 4
   AND name NOT IN (N'SSISCatalog', N'distribution', ...);