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!
Leave a Reply