Menu & Search
Change Default Database in SQL Server

Change Default Database in SQL Server

When you log into SQL Server using SQL Server Management Studio (SSMS), opening a new query window will automatically scope you within the default database set when your user was created. If this isn’t explicitly set during creation it’ll be set to the master database which isn’t very convenient for users. DBA’s might have to remember to set new login default databases to give a better user experience for SQL Server users.

What this means to a SQL Server user is, if they open a new query window it’ll likely be scoped to the master database. Most users shouldn’t need to query anything within the master database, so once connected they’ll have to run the USE command (link) or select the desired database within the GUI drop-down menu:

But with that said, users can stay within the master context and run queries as long as the database name is included (e.g. SELECT * FROM [Database_Name].[Schema_Name].[Table_Name]).

There’s also another option within the SSMS connection options for users to select the name of the database for the connection, but I’ve found that this doesn’t change your default database.

And lastly, if the default database is set to a User Database and the user doesn’t have permissions to access it, they’ll be prompted with the following error:

But don’t worry, the person creating the user account won’t set your default database to a User Database that you don’ thave access to!


Share

1 Comment

  1. […] using SQL Server Management Studio (SSMS) your database context will be automatically set to your default database which was defined during the creation of your SQL […]