search

microsoft / azuredatastudio

Azure Data Studio is a data management and development tool with connectivity to popular cloud and on-premises databases. Azure Data Studio supports Windows, macOS, and Linux, with immediate capability to connect to Azure SQL and SQL Server. Browse the extension library for more database support options including MySQL, PostgreSQL, and MongoDB.
https://learn.microsoft.com/sql/azure-data-studio
MIT License
7.56k stars 898 forks source link

Database Tree Missing If Login Has Default Database #590

Open BingoDinkus opened 6 years ago

BingoDinkus commented 6 years ago

It seems that if a database is specified when creating a connection, the database tree is omitted from the object explorer. What's worse, this configuration persists, even if the connection is deleted and re-created. I cannot find a way to connect to this server and have a DB tree option.

Steps to Reproduce:

  1. Create a new connection and include a Database
  2. Delete the connection
  3. Recreate the connection and leave the database option blank
  4. Connect to the server. The database will be included and the database tree will be missing from the object explorer.

Edit

Correction: This actually seems to be related to the user account on the SQL Server providing a default database. Changing my default database (on the server I have Admin rights on) to "master" seems to have brought the "Database" tree option back for this one server.

Steps to Reproduce:

  1. On the SQL Server, configure the Login to have a default database
  2. Connect to the server with SQL Operations Studio
  3. No Database tree/folder is available
  4. Disconnect from the server
  5. Change the login's default database to "master"
  6. Reconnect to the server with SQL Operations Studio. The "Database" tree will be available again.

Connected with Login's Default Database set to a specific database: image

Same server, connected with Login's Default Database set to 'master': image

BingoDinkus commented 6 years ago

Correction: This actually seems to be related to the user account on the SQL Server providing a default database. Changing my default database (on the server I have Admin rights on) to "master" seems to have brought the "Database" tree option back for this one server.

MattIrv commented 6 years ago

Thanks for the updated info. I can confirm this repros for me too when the default database is not master

haydnlj commented 6 years ago

If you set your SQL Ops Studio connection to have the default database of master, then the tree is available (regardless of whether your user has a default database set)

MattIrv commented 6 years ago

This is fixed now and will be part of the February release. If you connect using a system database name or by leaving the database name blank, SQL Ops Studio will now always show the list of databases and other system objects.

If you need the fix ahead of the February release, it is included in our latest insiders build: https://github.com/Microsoft/sqlopsstudio/releases

BingoDinkus commented 6 years ago

This issue seems to have returned awhile ago and is still happening in the June release.

MattIrv commented 6 years ago

@BingoDinkus is your connection set up to connect to the default database or does it explicitly use the database name?

I can't reproduce this when my connection is set up the first way (it will say '' next to the server name in Object Explorer, but it does happen when my connection is explicitly to my database (it will say whatever the database name is next to the server name in OE), which is in line with our design for the fix.

BingoDinkus commented 6 years ago

Sorry for the delayed response.

It seems things are working as intended, just not as I would have expected, based on the behavior of SSMS. In SSMS, the default database setting applies to new queries, so that you don't start in the "master" database every time you create a new query. With the current implementation, we're forced to choose between:

A. See all databases on the server, but have every single new query default to the master db B. Be completely unable to see other databases (and more importantly, table structures) on the server, but have the convenience of new queries default to a commonly used db.