No databases visible in SQL Server Management Studio

Question

I'm trying to determine what databases are present on an SQL server.

Can I assume that if I do not see any in management studio, then there are none? This article from Microsoft seems to suggest that if I don't have permissions to a particular database then I won't see it.

If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.

Answer 1

Open SQL Server Management Studio (SSMS) and run the following code to see what server role membership you have:

SELECT spr.name
    , sp.name
FROM master.sys.server_principals sp
    LEFT JOIN master.sys.server_role_members srm ON sp.principal_id = srm.member_principal_id
    LEFT JOIN master.sys.server_principals spr ON srm.role_principal_id = spr.principal_id
WHERE sp.sid = SUSER_SID();

The results look something like:

╔══════════╦════════════╗
║ RoleName ║ MemberName ║
╠══════════╬════════════╣
║ sysadmin ║ MyUser     ║
╚══════════╩════════════╝

If the result shows sysadmin in the first column, then you have the highest level of privilege available under SQL Server. Running the following code, in a "New Query Window", will show you definitively the databases present in the SQL Server instance, except the system databases, master, tempdb, msdb, and model:

SELECT d.name
    , d.create_date
    , d.is_encrypted
    , d.state_desc
    , d.user_access_desc
FROM sys.databases d
WHERE d.database_id > 4
ORDER BY d.name;

If the list is empty, and you run that as a member of the sysadmin role, you can be certain there are no databases connected to the SQL Server instance. Having said that, there may be database files existing on the server that have been detached. Locating detached database files is a tricky job since you cannot rely on the files having any specific names or file extensions.

SQL Server is capable of being installed multiple times on a single server, via what is known as "named instances". You should check the server's service list, via the services applet in control panel, for service names like "SQL Server (*)", where the * would be the name of the instance. If there are mutliple instances on the server, you should check each instance separately using the code I provided above.