Home arrow SQL Server arrow Page 8 - Sample Chapter: Wrox Beginning SQL Server 2000 Programming

Sample Chapter: Wrox Beginning SQL Server 2000 Programming

Wrox are well known for their "How-To" series of books, which teach beginning-intermediate developers how to use Microsoft technologies properly and efficiently. Today, Mitchell takes a look at chapter three from "Beginning SQL Server 2000 Programming", which is published by Wrox Press.

Author Info:
By: Mitchell Harper
Rating: 4 stars4 stars4 stars4 stars4 stars / 23
January 04, 2002
  1. · Sample Chapter: Wrox Beginning SQL Server 2000 Programming
  2. · Chapter 3: Query Analyzer (contd.)
  3. · Chapter 3: Query Analyzer (contd.)
  4. · Chapter 3: Query Analyzer (contd.)
  5. · Chapter 3: Query Analyzer (contd.)
  6. · Chapter 3: Query Analyzer (contd.)
  7. · Chapter 3: Query Analyzer (contd.)
  8. · Chapter 3: Query Analyzer (contd.)
  9. · Chapter 3: Query Analyzer (contd.)
  10. · Summary

print this article

Sample Chapter: Wrox Beginning SQL Server 2000 Programming - Chapter 3: Query Analyzer (contd.)
(Page 8 of 10 )

Altering the Default Database for a User

When you enter Query Analyzer, there is always a database selected. This is called the default database, and it is the database that has been defined as the main database that this user will be working with. Don't worry it isn't saying that this is the only database that you can work on. It's just one way of saving a few seconds by having a specific database selected and ready to start dealing with. You can change the default database for a user within Enterprise Manager, or through a command in Query Analyzer. This process was covered to some extent earlier when using key shortcuts to run system commands. We also saw how to alter the database that is being used temporarily, using the database combo box. This will not affect the user's default database setting.

By setting the default database, not only will you be saving yourself time when you open up Query Analyzer, but it will also help you to avoid developing solutions in the wrong database and then apparently losing your changes when you do move to the right database.

First of all, let's check out the first of these methods, which is within Enterprise Manager.

Try it out Altering the Default Database in Enterprise Manager

1. Start up Enterprise Manager and navigate to your SQL Server installation. In my case, this is WONDERBISON.

2. Expand the Security tab and click on Logins icon:

3. Find the User ID to alter and double- click on it. I chose BUILTIN\Administrators, which relates to any User ID defined in Windows 2000 as an Administrator, but is without a specific User ID within SQL Server. I could have chosen my login, RDewson, but I am saving this for later. As you can see, the Database defined in this dialog is set to master:

As you read in Chapter 1, master is a major system database and you should never really be in it, so let's alter this default straight away.

4. Under Defaults, change the Database to another database. There are only two non-system databases at the moment: pubs and Northwind. Select one of these. I have chosen Northwind:

5. Once you have made you selection, click OK.

6. This will take you back to Enterprise Manager. Check the Enterprise Manager Details pane and you will see the Default Database has altered and will now show the database you chose in my case, Northwind.

That's all there is to it. Now, when I log in to Query Analyzer using an NT account that belongs to the Administrators' group, then SQL Server will log me in as BUILTIN\Administrators and will default to the Northwind database. For example, if Annette Kelly logged in with her User ID of AKelly, then, as she is not specifically listed in SQL Server, and as she was set up as an Administrator user in Chapter 1, she will automatically be pointed towards Northwind to start with.

What Enterprise Manager is, in fact, doing is altering a system table within the master database to reflect these changes, as Query Analyzer refers to this system table to know what the default database is for each user. It is as simple as that.

Earlier on, we created a new login called RDewson. The default database was set to master. It is now time to alter that database to be Northwind too. This is an even simpler process in QA.

Try It Out - Altering the Default Database in Query Analyzer

1. Ensure that Query Analyzer is still running.

2. In the Query pane, enter the following:

EXEC sp_defaultdb "WONDERBISON\RDewson","Northwind"

3. You should see the following result:

Default database changed.

4. If you check in Enterprise Manager, and complete a refresh, you will see that RDewson now has a default database of Northwind:

This works in exactly the same way as altering the default database did in Enterprise Manager, but without the pretty interface. Under the hood, Enterprise Manager runs the same piece of code that you saw above, but it just hides this from you.

Of course, you can also alter the database temporarily within Query Analyzer, without affecting the defaults at all. We have seen this when we looked at the database combo box before.

This next section will show you how to temporarily alter RDewson from Northwind to the pubs database, and will check that this is only a temporary change.
blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials