FEAT: Database Dropdown Should Auto-Populate Regardless of Current Database

[AustralianRaven]

Summary

The database selector dropdown in the sidebar does not populate with available databases unless the user is currently connected to master. If the active database is any other database (e.g. [DB_SOURCE]), the dropdown appears empty and the user cannot switch to another database (e.g. [DB_TARGET]) without first manually navigating to master to trigger the list.

---

Current Behaviour

1. User connects to SQL Server with default database set to [DB_SOURCE].
2. The database dropdown in the sidebar shows the current database name but lists no other options.
3. User manually selects master from the dropdown — the full list of databases now appears.
4. User can then select [DB_TARGET] from the populated list.

This is an unnecessary two-step workaround.

---

Expected Behaviour

The database dropdown should always be pre-populated with all databases the current user has access to, regardless of which database is currently active. Switching databases should be a single-step action at all times.

---

Steps to Reproduce

1. Configure a SQL Server connection where the default database is not master (e.g. set it to any named database).
2. Connect.
3. Observe the database dropdown — it is empty aside from the active database.
4. Click master in the dropdown.
5. The full database list now appears.

---

Root Cause Analysis

1. listDatabases runs in the context of the current connection

File: apps/studio/src/lib/db/clients/sqlserver.ts:598

async listDatabases(filter: DatabaseFilterOptions) {
  const databaseFilter = buildDatabaseFilter(filter, 'name');
  const sql = `
    SELECT name
    FROM sys.databases
    ${databaseFilter ? `AND ${databaseFilter}` : ''}
    ORDER BY name
  `
  const { data } = await this.driverExecuteSingle(sql)
  return data.recordset.map((row) => row.name)
}

sys.databases is a server-level catalog view. In SQL Server, users without a VIEW ANY DATABASE or VIEW DATABASE STATE server-level permission can only see databases they have direct access to when this view is queried from a non-master connection context. When the active connection is master, SQL Server typically grants broader visibility, which is why the list appears correctly only from that context.

There is also a latent SQL bug on line 603: the filter branch generates AND <filter> without a preceding WHERE, which would produce invalid SQL if a filter were ever passed. This is dormant today because updateDatabaseList calls listDatabases() with no arguments.

2. updateDatabaseList uses the post-switch connection context

File: apps/studio/src/store/index.ts:530-543

async changeDatabase(context, newDatabase: string) {
  await Vue.prototype.$util.send('conn/changeDatabase', { newDatabase: databaseForServer });
  context.commit('database', newDatabase)
  await context.dispatch('updateTables')
  await context.dispatch('updateDatabaseList')   // <-- fetches from the new (non-master) context
  await context.dispatch('updateRoutines')
},

When the user switches to [DB_SOURCE], the updateDatabaseList call that follows uses the new connection to [DB_SOURCE]. If that connection context limits sys.databases visibility, the store ends up with a truncated or empty databaseList.

3. Dropdown options are derived directly from the (now empty) store list

File: apps/studio/src/components/sidebar/core/DatabaseDropdown.vue:147

availableDatabases() {
  return _.without(this.dbs, this.selectedDatabase)
}

this.dbs maps to store.databaseList. When that list contains only the current database (or nothing), _.without removes it and the dropdown renders with zero options.

---

Proposed Fix

Option A — Use HAS_DBACCESS to query only accessible databases (minimal change)

Replace the raw sys.databases query with one that explicitly filters to databases the current login can access:

// apps/studio/src/lib/db/clients/sqlserver.ts
async listDatabases(filter: DatabaseFilterOptions) {
  const databaseFilter = buildDatabaseFilter(filter, 'name');
  const sql = `
    SELECT name
    FROM sys.databases
    WHERE HAS_DBACCESS(name) = 1
    ${databaseFilter ? `AND ${databaseFilter}` : ''}
    ORDER BY name
  `
  const { data } = await this.driverExecuteSingle(sql)
  return data.recordset.map((row) => row.name)
}

HAS_DBACCESS works correctly from any database context and returns 1 for every database the current login can connect to, regardless of server-level permission grants. The missing WHERE is also fixed here.

Option B — Execute listDatabases via the master database connection (more robust)

For SQL Server specifically, open a secondary connection to master solely for metadata queries like listDatabases. This ensures consistent visibility regardless of the user's default database. This is a larger change but eliminates the permission-context dependency entirely.

---

Acceptance Criteria

• Connecting to SQL Server with any default database populates the dropdown with all accessible databases immediately on connection.
• Switching from one database to another updates the dropdown correctly without requiring a detour through master.
• The fix does not regress behaviour for other database engines (PostgreSQL, MySQL, etc.).
• The latent AND <filter> SQL bug in sqlserver.ts:603 is resolved.

---

Affected Files

File	Relevance
apps/studio/src/lib/db/clients/sqlserver.ts:598	listDatabases SQL query and context
apps/studio/src/store/index.ts:530	changeDatabase action — calls updateDatabaseList post-switch
apps/studio/src/store/index.ts:577	updateDatabaseList action
apps/studio/src/components/sidebar/core/DatabaseDropdown.vue:147	availableDatabases computed — renders the dropdown options

[matt-bowles]

Please address with highest priority.