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