What is the difference between master and default

Posted on 2011-05-06
Last Modified: 2012-05-11
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
Question by:fstinc
    LVL 3

    Accepted Solution

    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).

    LVL 28

    Assisted Solution

    by:Ryan McCauley
    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").

    Author Closing Comment

    Thanks a mil.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now