This blog post contains a quick guide on how to change your default database when you or your users next log in to SQL Server via SSMS.

When you log into SQL Server using SSMS and open a new query window, you will automatically be set to use your logins default database which was configured during the creation of your SQL User. Sometimes a user might be set to use a database they don’t have access to, and will be prompted with an error message:

Cannot open database “DatabaseName” requested by the login. The login failed.

The default database of a SQL user is a more important factor if managing permissions on a shared (customer facing) SQL Servers.

Changing Default Database for a SQL User

Create Schemas & Table

First, we will open a new query window with a default database setting of ‘master’.

When we query a table like sys.tables, it’ll show the tables within the master database. If we want it query other databases we can swap to use another database using the drop-down menu as shown above. There’s a few other ways to change the database you are querying, for example using the USE command or just add the the database to our query with three-part-identifier (databaseName.schemaName.tableName).

Example 1: Change a SQL Users Default Database via Command

To change the default database for a SQL Server login via a command, you can use the ALTER LOGIN statement. Here’s the general syntax:

-- change default database of sql user
ALTER LOGIN [LoginName] WITH DEFAULT_DATABASE = [DatabaseName];

Example 2: Changing a SQL Users Default Database via GUI

To change a SQL users default database using SQL Server Management Studio (SSMS):

1. In Object Explorer, navigate to Security > Logins.
2. Find the user login, filter results by right-clicking if helpful.
3. Right-click the SQL user and select Properties.

The Login Properties window will show on screen, and at the bottom of the General tab we can see and amend the default database setting.

If you are unsure what to set this to, you can go ahead and set the default database to what the one you think the user will be querying most often.

Hope this helps!