[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

What is the difference between master and default

I have a user set up for readOnly privileges to the database. He was running SQL 2000 Query Analyser but could not login to the database even though he had all of the appropriate permissions. So, one suggestion was to upgrade to SQL Server Management Studio. Now we get the following:

Error: 4064 – Cannot open user default database. Login failed. Login failed for user

I have found a workaround by doing the following:

First click on Option>> Button of “Connect to Server” Prompt.

Now change the connect to database to any existing database on your server like master or msdb.

However,  is there anything dangerous about changing this setting to <Master>?

I read a forum that this should resolve the issue however after login in under <Master> it also says to do the following:

Once you login in the system run following statement for your username and it should fix your problem. Make sure to replace [test] with your username and master with yourdatabase name.

Is there anything that can go wrong? I am getting confused as to what MASTER database the user will connect into. Thanks in advance.d
2 Solutions
I am guessing your user is trying to connect to their default database. From SQL Enterprise Manager, if you go to Security > Logins > and the user account, the default database will be defined at the bottom of the screen. This is the database that a login connects to if it doesn't specify a database at connection time.
The error that you are getting is because even though the database may be set as the user's default database, the user hasn't been granted permissions to connect to that database. You will need to go to "User Mapping" section (look at the top left of the Login Properties screen) and grant the user access to the required database from the right hand side of the screen. Ensure that when you check the Map box, the User column is populated with the account name. Then from the bottom half you can define how this account accesses the database e.g. check the db_datareader role for Read Only access.

If you haven't given the account access to a user database as I have described above, then you may be connecting to the master database. There is no harm if this is done BUT it is not recommended to leave a user account with master as the default database in case they mess it up (which can mean messing up the SQL server).

Ryan McCauleyCommented:
Everybody is granted guest access to Master, since they need that access to list the databases on the server that they have access to (the SQL Server automatically uses permissions to hide the ones they can't log in to). If a user has a login on the server that's currently enabled, and their default database is set to "master" (which is the default when you create the user unless it's changed), then the user will be able to log in without specifying a database.

If the default database changes to something else, make sure the user at least has a login for that database, or that the guest account has been granted some kind of permissions - otherwise, they'll be denied access to that database and they'll be unable to log in (unless they specify a database that they do have access to).

In either case, specifying a database they have access to in their login attempt will always work. If you want a login attempt that doesn't specify a database to also work, they'll need rights to whatever is set as their default database (or they'll need the default changed back to "master").
fstincAuthor Commented:
Thanks a mil.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now