MSSQL - Change default database by sql query

Dear reader,

Recently I noticed in my daily work that every time I logged in [MS SQL] (Note: The original article is no longer available online) I was required to select the database which I had to work on and this was regularly in use by me and this lead me to find the solution to change the default database selection after I log on to MS SQL using SSMS to reduce the hassle and also so that I don't execute queries on different [databases] (Note: The original article is no longer available online) by mistake.

There is an inbuilt [stored procedure] (Note: The original article is no longer available online) name sp_defaultdb which can be used as below

Exec sp_defaultdb @loginame='Domain/UserName', @defdb='MyDefaultDatabaseName'

Here value of parameter @loginname is User Name such as 'sa' and it can change depending on the [MSSQL] (Note: The original article is no longer available online) login configurations such as Sql Authentication or [Windows Authentication] (Note: The original article is no longer available online). Example shown here is for Windows Authentication. Then value of parameter @defdb is the database name such as 'master' or 'AdventureWorks' and it will change as per the need to set the default database for the particular login specified.

But this stored procedure seems soon to be obsolete as mentioned in [MSDN] (Note: The original article is no longer available online) link here. There is newer option available for this purpose - ALTER LOGIN which has many other usability apart from setting the default database but I would stick to the topic and show its usage.

ALTER LOGIN [Domain\UserName] WITH DEFAULT_DATABASE = [MyDefaultDatabaseName]

I hope you found this helpful and learnt something new from this.

Reference links: StackOverflow - How can I change my default database in SQL Server without using MS SQL Server Management Studio?

Keep your comments flowing for what you prefer. :)