Home arrow SQL Server arrow Page 7 - 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 7 of 10 )

Try It Out Altering the Database

1. Ensure that Query Analyzer is running. It doesn't really matter who you are logged in as at the moment.

2. You should be connected to the Northwind database from the earlier example. Just to prove that Northwind is the current database, enter the following code in a new query pane and then execute it:


3. You should see something like the following in the Results pane (the width has been shrunk so it fits on the page):

TABLE_QUALIFIER TABLE_OWNER TABLE_NAME -------------- ----------- -----------

Northwind dbo syscolumns

Northwind dbo syscomments

Northwind dbo sysdepends

Northwind dbo sysfilegroups

Northwind dbo sysfiles


4. Click the down arrow on the right side of the database combo box. This will bring up a list, as shown below:

5. Select pubs. This will alter the database that you are pointing to.

6. Now execute the sp_tables code again, and you will see the following results:

TABLE_QUALIFIER TABLE_OWNER TABLE_NAME --------------- ----------- ----------

pubs dbo syscolumns

pubs dbo syscomments

pubs dbo sysdepends

pubs dbo sysfilegroups

pubs dbo sysfiles


7. Once you are done, select the Northwind database in the combo box again.

There is another method for altering databases within Query Analyzer. It is possible to use a command, USE, which when combined with the name of the database will also alter which database the Query pane is working with. So, USE Northwind, which is then executed, would change to the Northwind database from that point onwards. Keep in mind though, that it is for that Query pane only.

Display Estimated Execution Plan (Ctrl+L)

When you have built a set of code, which is going to access data in a database, it is possible to check how well the query should run using this option. We won't go into any more detail here, except to say that this option shows how well your code has been built, and highlights areas that could be optimized.

Object Browser (F8)

This is used to toggle the display of the Object Browser on or off. Useful for creating more screen space when writing lengthy queries.

Object Search (F4)

This helps you search for particular objects. An object can be a table, a column, or any other piece of information that is stored in a database. The Object Search utility is a savior when using large or unfamiliar databases.

Let's take a look at how this works.

Try It Out Object Search

1. Imagine that we have just received a call requiring us to find out the year-to-date sales of books. We know the name of the column, ytd_sales, but can't remember which table or database it is in.

2. Click the Object Search button, which brings up the Object Search screen:

3. In the Object name combo box, enter ytd_sales. You can also place wildcard characters if required, for example you could have entered ytd_sal*.

4. If we knew that we had to look within the pubs database, we could select pubs in the Database combo box. For the moment, leave it as :

5. Leave the Hit limit set to 100. We know that we want to find a column so, in the object types check boxes, uncheck the User table check box and select Column instead.

6. Click the Find Now button on the top right side.

7. When the search is finished, you should see two items listed as the Object Search has found two object_names matching the search:

 {mospagebreak title=Chapter 3: Query Analyzer (contd.)&toc=1} Current Connection Properties

This is exactly as was discussed earlier in the chapter; it shows the current settings for the current server connection.

Show Results Pane

This toggles whether you see the Results pane or not. Good when you are not running any query and are building up a large amount of SQL.

Using Query Analyzer

By this stage, you should know your way around Query Analyzer and should be ready to start using it properly. Let's start using it in earnest. I won't show you any queries dealing with data yet; there are plenty of examples of that later in the book. The first example will show you how to create a new login account.

Creating a New Login Account

You will have noticed that the only IDs seen so far in this chapter are RDewson, sa, and Builtin\Administrator. Do you remember that, in Chapter 1, new User IDs were created when we demonstrated the authentication modes? Well, it's time to allow a specific User ID to be placed into SQL Server. If you remember, Annette Kelly was an NT administrator, so she could log in to SQL Server already because there is an administrator login. However, Jack Mason couldn't. I think it is time to let User ID JMason log in.

Try It Out Creating a New Login Account

1. Ensure that Query Analyzer is up and running and that you are logged in to an account, which has administrator privileges. I will use the login created when SQL Server was installed, RDewson.

2. In the Query pane, enter the following code not forgetting to change WONDERBISON to your own computer, or network name. Once entered, run this code by either pressing Ctrl+E, F5, or the Execute button on the toolbar:

EXEC sp_grantlogin "WONDERBISON\JMason"

USE Northwind


EXEC sp_grantdbaccess "WONDERBISON\JMason","JMason"

EXEC sp_addsrvrolemember 'WONDERBISON\JMason',"sysadmin"

This example uses double and single quotes, so you may get an error when running it. If you do, check back to the Set quoted_identifier subsection of the Query | Current Connection Properties section to see where you are going wrong.

3. You should see the following in the Results pane:

Granted login access to 'WONDERBISON\JMason'.

Granted database access to 'WONDERBISON\JMason'.

'WONDERBISON\JMason' added to role 'sysadmin'.

4. Now move to Enterprise Manager and find the Logins node within the Security node. Refresh by right-clicking on the Logins node and selecting the Refresh option. You will then see the new JMason ID listed alongside the other login IDs. If you then right-click on the JMason login ID and select Properties, you will see the following dialog:

5. If you want to modify or inspect this login ID further, you may get the following error message. However, if you leave Enterprise Manager and start it up again, the message disappears. This seems to be a timing issue within all the processes, which shouldn't exist, but unfortunately does when adding a user through Query Analyzer.

How It Works

Query Analyzer is performing several actions over several lines of code to achieve a full new login for Jack Mason. First of all, it takes the User ID of JMason, found in the Users and Passwords section of Windows NT/2000, and then grants this user with a specific login to SQL Server, and therefore once again, removes the need to log in with a generic ID, or worse still, "sa". In the past, Jack would not have been able to log in either under his own ID or with a group ID, as there is only an Administrator group ID set up and Jack does not have an Administrator User ID. Therefore, to allow Jack to use SQL Server using Windows NT Authentication, we have to grant him a log in. We created a SQL Server account in the previous chapter for DTarbotton so you can compare the differences. So, the sp_grantlogin gives Jack a User ID in to SQL server.

EXEC sp_grantlogin "WONDERBISON\JMason"

However, as it is, this is still not of much use. Yes, Jack could log in to SQL Server, but he would not be able to complete any work because he still does not have any rights to any of the databases. All he has is a login. This is where the remaining lines of code come in to action. I would like Jack to be able to use the Northwind database. Therefore, I have to specifically tell SQL Server that this is to be the case. Query Analyzer is moved to the correct database using the USE command:

USE Northwind


Once the Northwind database is selected, it is possible to begin the actions required to give Jack full rights to the database. Without getting into too many technicalities here, the next line grants Jack access to Northwind:

EXEC sp_grantdbaccess "WONDERBISON\JMason","JMason"

The final line of code gives him a role within SQL Server, which grants him full system administration privileges. Jack can now perform any action that he likes within the Northwind database:

EXEC sp_addsrvrolemember 'WONDERBISON\JMason',"sysadmin"

Once all of these lines of code have executed, Jack is a valid member of SQL Server and the Northwind database.
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-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials