Home > Sql Server, Tips/Tricks > How to set default database in Sql Server Mangement Studio. A Time Saving Tip

How to set default database in Sql Server Mangement Studio. A Time Saving Tip

In SSMS whenever we connect to any database server by default the MASTER database is the default selected one. Which forces us  in changing the database the one we want work using the USE statement or by using mouse and changing the selected db from the drop-down list.This is were anoying, and till recently daily I was wasting time in doing this multiple times.

If we know that by default most of the time we will be connecting to a specific database related to our project, then Sql Server Management Studio (SSMS) provides a way to change this. And next time onwards this nerw selected database will selected by default instead of the MASTER DB.

Steps: 1) Open Sql Server Management Studio
 2) Go to object Explorer -> Security -> Logins
 3) Right click on the login and select properties
 4) And in the properties window change the default database and click OK.

Alternatively, we can change this by below statement aswell:

Exec sp_defaultdb@loginame='TestUser', @defdb='Test'

Note: Please don’t experiment this on production database and also User should have access to the database to which we are changing.

Please correct me, if my understanding is wrong. Comments are always welcome.

Categories: Sql Server, Tips/Tricks
  1. May 21, 2012 at 4:59 PM

    Thank you for this useful tip. Also using “Registered Server” option of SSMS user can easily manage his connection properties such as user id, password and default database. http://mcaf.ee/9z8oc

  2. August 24, 2012 at 4:30 AM

    Thanks also for the tip information! On SSMS 2008, I had to do the following: right-click on the login and select Facets. Then change the DefaultDatabase field to the desired database (I had to type it in rather use a pulldown list since no existed).

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: