SQL Server Performance

Change Default Database

Discussion in 'SQL Server 2005 Analysis Services' started by ranjitjain, May 18, 2006.

  1. ranjitjain New Member

    Hi,<br />I'm really not able to find way to change the default database in AS2005.<br />I use SQL Management studio and make connection to my server.<br />It connects to one default database and i always get error when i run any MDX query as cube does not exist(which is in other database), so i then change the available database listbox to point to my DB always which is really annoying.<br /><br />Can you please let me know about setting my Database as default DB whenever any connection is made to this server.[<img src='/community/emoticons/emotion-6.gif' alt=':(' />]
  2. satya Moderator

    Have you searched on AS BOL for this information>?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. ranjitjain New Member

    quote:Originally posted by satya

    Have you searched on AS BOL for this information>?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

    Hi Satya,
    Yes i searched over BOL, google,google groups but could not find any clue.
  4. satya Moderator

    .. SQL 2005 BOL look for databases [Analysis services] --> modifying (refer to this section for further information).

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. ranjitjain New Member

    Hi Satya,
    I read whole section about modifying databse but did not found anything which can help me to set my databse as default DB for all.
    Section deals about securing AS.

    Anyways what i did is while connecting to instance dialog box in SSMS, clicked advanced and changed the database to connect to which solved my problem.
    So for my credentials my db is default for me.

    Thanks for the help.
    But if you find any other way to set default DB for all in AS, please post it.
  6. satya Moderator

    Sure, AS is not an everyday job for me here.. .but as soon as I get the information will post here.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. Flying-Squirrel New Member

    I know this is a very old topic and you probably don't need the answer any more but perhaps other searching on this issue can benefit from this answer. I had the same problem you had I think after deleting the database that had accidentally been set to 'default' and nothing working correctly thereafter.
    Make a new query and enter: alter login YourLogin with default_database = master

    It worked for me, hopefully it will work for others. Cheers!
  8. satya Moderator

    True that a problem in keeping user database as a default database until SQL 2000 version, but not anymore with SQL 2005 I blieve.
  9. ranjitjain New Member

    Hi,
    Actually another easy way is when we connect to as server, in that window under options we can specify which database we want to connect and which gets stored internally
  10. satya Moderator

    I think that is valid for Mixed mode authentication, not in case of windows authentication.
  11. Strotzyl New Member

    It worked for me also! Thanks for this query. [:D]
  12. TulsaDavid New Member

    ranjitjain has it right. If you are going in via Win Auth, are in a group, are using SA or some other userid, or are in a situation where changing your login is not really the solution, AND if all you wish to do is default to a database in the query editor:

    * In an existing query editor, simply right-click, select Connection, Change Connection.

    * Click the Options button.

    * In the Connection Properties tab, select the database you wish to connect to.

    SSMS will remember your selection for that server. You may have to repeat for other servers, but it does remedy having a default database other than master.
  13. Luis Martin Moderator

    Welcome to the forums!!
    Thanks for you collaboration.
    Always check post date. This one is 3 years old.

Share This Page