How to: Set default database in SQL Server

Happy New Year to you all, Its been while I have stopped blogging. To get started back to blogging I have started with this small tip which saves your time. After all time is what we don’t have these days.

In SSMS whenever we connect to any database server by default the master database. Which forces us in changing the database to the one we want work using the USE statement or by using mouse and changing the selected db from the drop-down list. So this we will be doing this all-day and wasting time by doing this multiple times.

As a developer by default we mostly connect to single database at times specific to our project. So SQL Server Management Studio (SSMS) provides a way to change this. And next time onwards this new selected database will selected by default instead of the MASTER DB.

Here are the Steps:

1. Open SQL Server Management Studio
2. Select the instance you want to configure and connect to it by using your preferred authentication
3. Go to object Explorer -> Security –> Logins
4. Right click on the login and select properties
5. And in the properties window change the default database to the one you want to select and click OK.

Alternatively, we can change this by below simple statement as well:

-- @loginame --login name is the you use for SQL authentication
-- @defdb -- name of the default database 
Exec sp_defaultdb @loginame='sa', @defdb='Test'