How to Set Default Database in Sql Server Management Studio?

Whenever you connect to any database server in SSMS, by default Master database is selected as current database. But you work on different database and every time you have to change database either by using USE statement or changing selected db by mouse. It is very irritating task. You can overcome it by setting a default database to that db server. In this article I have described how to do this setting.

Really this is very annoying and time wasting task to change the database the one you want to work. If you know that most of time when you connect database server, you use a specific database then you can set that database in Sql Server Management Studio (SSMS) as a default database for a specific database server. Every time when you will connect database server then that database will be automatically selected in place of MASTER database. You can do it by 2 methods:

Method 1: By Using GUI

You need to follow following steps:

  • Open Sql Server Management Studio (SSMS)
  • Connect to database server for which you want to set default database
  • Open Object Explorer
  • Security -> Logins
  • Right click on the login for which you want to set default database and select properties
  • Select properties
  • Change default database in properties window and click OK.




Method 2: By Running Query

You can set your default database using the sp_defaultdb system stored procedure. You need to follow following steps:

  1. Open Sql Server Management Studio (SSMS)
  2. Connect to database server for which you want to set default database
  3. Click on New Query button or press Ctrl + N for opening new query window
  4. Run following query in new query window

Exec sp_defaultdb @loginame='<login_name>', @defdb='<db_name>'

login_name: Name of the login for which default database needs to be selected
db_name: Default database name