Change Default Database | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Change Default Database

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=’:(‘ />]
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.
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.

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

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

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

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

It worked for me also! Thanks for this query. [:D]

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.
Welcome to the forums!!
Thanks for you collaboration.
Always check post date. This one is 3 years old.
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.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |