Setting Default SQL Server Database Using ISQL

Problems????

Image by essecento via Flickr

You might be familiar with the following scenario:

You have a SQL Server database server hosting more than one database, but you just so happen to be accessing one in particular from your development environment (Query Analyser, Management Studio, etc).

So, you run a little query to automatically connect you to this database when you connect to the server. You might try something like:

Exec sp_defaultdb @loginame='DOMAIN\UsrNam', @defdb='SomeDatabase';

So, now when you log on, your database is ready and waiting to be used without having to manually select it. I have found this scenario common in development environments, when you are typically running lots of ad-hoc SQL queries, or running in code (stored procedures, views, triggers, etc) against the database.

However if you are working on different databases on the same server, defaulting to a particular one can cause you problems if you need to run in code against a different database. On occasions I have applied stored procedures to the default database, when I meant to do it against another one on the server!

Another consequence is when a development database is refreshed or rebuilt from some external source (from a live environment, for example). Although your default database may exist by name, this is not enough for SQL Server, which fails to log you on and gives the error message:

Cannot open user default database.

You will also get this if you have chosen to rename the database.

So what to do in this instance?

Well, we can defer to the trusty ISQL command line utility, as shown in the following screenshot:

Simply follow these steps:

  1. Open a DOS  command Prompt
  2. Open up an ISQL Session. I used the following command:
    • -S SQLServer_Name – Specify the name of the SQL Server to which you are connecting.
    • -E – Stipulate to connect as a trusted connection (Windows Authentication)
    • -d master – The database name to which you are going to connect. Here I specified ‘master’
  3. isql -S SQLServer_Name -E -d master

    The three options user above are as follows:

    Of course, you can connect however you want. For more options (also shown in the screenshot above), type in isql /? for a full list, or refer to the site on the above link.

  4. Now run a version of the above script, setting the default database to master
  5. Exec sp_defaultdb @loginame='DOMAIN\UsrNam', @defdb='master';
  6. To apply this, enter ‘GO’ on the next line.

This will ensure your SQL Server defaults you to the ‘master’ database in all subsequent connections (unless you change them again). I would counsel this as general good practice, and it comes recommended by a friendly DBA I happen to know.

Leave a Reply