When you log into SQL Server using SQL Server Management Studio (SSMS) and open a new query window, you will automatically be set to use your logins default database which was set during the creation of your SQL User.
This guide shows how to change your default database when you or your users next log in to MSSQL via SSMS.
How to Change Default Database for a SQL User
First, we will show a new query window with a default database setting of ‘master’: The user would have to amend using the drop-down, or they could have selected the database within the Object Explorer before opening a new query.
To amend a users default database in SSMS:
– Navigate to Security > Logins in the Object Explorer.
– Find the user login. We can right-click to filter if there are a lot of users on the instance.
– Right-click the user and select Properties.
The Login Properties window will show. At the bottom of the General tab, we can change the default database to one that the user will be querying most often.
The next time this user ‘pete‘ logs into SQL Server Management Studio and opens a new query, the default database will be ‘cityBlock‘ rather than ‘master‘.
As ever, hope this was the guide you were searching for!