SQL Server Default Database when Query - master

Question

I've been using SQL Server Management Studio (SSMS) for the last 8 years, and I keep on stumbling upon a problem. When I right click on a Table, and select SELECT TOP 2000 ROWS, the query editor opens up a new file with the query inside. This is nice and all for a quick review of the table.

The problem I have is the default database is changed from the actual database to the master database. I have sysadmin rights.

The query that gets generated by SSMS, then has the databse, schema, and table in brackets, i.e. [DB].[dbo].[TableName]

Is there a way to set the default database on the SELECT TOP 2000 ROWS command, to NOT go and set the default database to 'master' ?

The other workaround is to click on the table, and then do a 'New Query', which will keep the current database, and then I have to type in 'SELECT * FROM TableName'

default database changed to master

You could do Script table as >> SELECT to >> New Query Editor Window but you would have to add in the SELECT TOP 2000 which is probably on a par with the amount of effort required to write USE Database and remove the database name from the select command — GarethD
– GarethD, Commented Jun 7, 2012 at 8:25
It would help if you didn't keep referring to this as the "default database" changing - it's a new connection, and it default to using your default database (that happens to be master) - it's not changing the default database, it's using it. And no, there's no way to change this behaviour. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 7, 2012 at 8:41

Barry Kaye · Accepted Answer · 2012-06-07 08:28:47Z

26

In SSMS go to Security > Logins - choose your login, then right-click and choose Properties - at the bottom of the tab is an option called "Default Database" - this is what you want to change.

answered Jun 7, 2012 at 8:28

Barry Kaye

7,7696 gold badges46 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GarethD Over a year ago

This only solves the problem if the table you want to select the top 2000 from is in the default database.

Sina Iravanian Over a year ago

In case you don't know where Security is: in Object Explorer, expand the folder of the server instance with which you want to work, Security resides there right after Databases.

Collectives™ on Stack Overflow

SQL Server Default Database when Query - master

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related