CHAPTER 18 • SECURITY AND SQL SERVER 2000
720
you have a well-designed security plan that incorporates growth, managing your user
base can be a painless task.
To limit administrative access to SQL Server at the server level, you learned that
you can add users to a fixed server role. For limiting access in a specific database, you
can add users to a database role, and if one of the fixed database roles is not to your
liking, you can create your own. You can even go so far as to limit access to specific
applications by creating an application role.
Each database in SQL Server 2000 has its own independent permissions. You
looked at the two types of user permissions: statement permissions, which are used to
create or change the data structure, and object permissions, which manipulate data.
Remember that statement permissions cannot be granted to other users.
The next section in this chapter described the database hierarchy. You looked at
the permissions available to the most powerful user—the sa—down through the
lower-level database users.
You then learned about chains of ownership. These are created when you grant
permissions to others on objects you own. Adding more users who create dependent
objects creates broken ownership chains, which can become complex and tricky to
work with. You learned how to predict the permissions available to users at different
locations within these ownership chains. You also learned that to avoid the broken
ownership chains, you can add your users to either the db_owner or the db_ddladmin
database role and have your users create objects as the DBO.
Permissions can be granted to database users as well as database roles. When a user
is added to a role, they inherit the permissions of the role, including the Public role,
of which everyone is a member. The only exception is when the user has been denied
permission, because Deny takes precedence over any other right, no matter the level
at which the permission was granted.
We then looked at remote and linked servers, and at how security needs to be
set up to make remote queries work. We finished with a look at n-tier security and
applications.

Other ADO Libraries 756
Summary 760
2627ch19.qxd 8/22/00 11:11 AM Page 723
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
I
n most applications involving SQL Server, not all of the development is done on
the server itself. This is the essence of client-server computing: Work is parti-
tioned between a central server and distributed clients. To view and modify
server-side data from a client application, one uses a client data-access library.
Over the years, Microsoft has released a number of client data-access libraries that
can use SQL Server data, including DB-Lib, Data Access Objects (DAO), and Remote
Data Objects (RDO). Although all of these libraries are still in use, they’re no longer
undergoing active development. Instead, Microsoft recommends that all new client
applications use ActiveX Data Objects (ADO) to interact with the server.
ADO is the only client data-access library that we’re going to cover in this book.
Even if you’ve used another library for that purpose in the past, you should consider
migrating to ADO to take advantage of current advances in the state of the art. In this
chapter, we’ll start by describing the ADO object model and then take a look at what
you can do with ADO. We’ll close with a brief section on ADOX, an ADO extension
designed to help you work with schema information.
ADO provides an object model atop the OLE DB interface, which is the low-level
“plumbing” that SQL Server uses between its own components. Because of this inti-
mate connection, ADO is a good choice for working with data stored in SQL Server.
The ADO Object Model
Figure 19.1 shows the ADO object model for ADO 2.6, the version that ships with SQL
Server 2000. An object model lists the various objects that a library contains and shows
their relationships. As you can see, the ADO object model is fairly simple.
FIGURE 19.1
The ADO object
model

provider of the object. As an application developer, you can use those methods and
properties to interact with the original product.
For example, ADO includes an object called a Recordset. This object represents a
set of records (for example, the results of a query) from a data provider. A Recordset
object can be used to represent any set of records. The Recordset object has methods,
such as MoveFirst (which positions an internal pointer to the first record in the
Recordset) and MoveLast (which positions an internal pointer to the last record in the
Recordset). It also has properties, such as RecordCount (the number of records in the
Recordset) and EOF (a Boolean property that indicates the last record of the Recordset
has been retrieved). The Recordset object also has events, such as FetchComplete,
which occurs when all of the records from an asynchronous operation are available in
the Recordset.
Objects can be arranged in collections, which are groups of similar objects. For
example, in ADO there is a Parameters collection of Parameter objects. You can use a
THE ADO OBJECT MODEL
Development with
SQL Server
PART
V
2627ch19.qxd 8/22/00 11:11 AM Page 725
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 19 • ADO AND SQL SERVER
726
collection to view each object in turn of a similar group. This is called iterating
through the collection.
Objects can also contain other objects. For example, the ADO Recordset object
contains a collection of Field objects, each of which represents one of the individual
fields in a record in the Recordset.
Objects provide an abstract view of the underlying software. It’s unlikely that
there’s actually a data structure within SQL Server that you could point to and say,

727
data and for commands that instruct SQL Server to do something, such as action
queries.
Think of a Command object as a single instruction to SQL Server to produce or
alter data. The easiest way to use a Command object is to create an independent
Command object, set its other properties, and then set its ActiveConnection property
to a valid connection string. This will cause ADO to create an implicit Connection
object for use by this Command only. However, if you’re going to execute multiple
Commands on a single Connection, you should avoid this technique, because it will
create a separate Connection object for each Command. Instead, you can set the
ActiveConnection property to an existing Connection object.
A Parameter object represents a single parameter for a Command object. This
might be a runtime parameter in a SQL query, or an input or output parameter in a
stored procedure. If you know the properties of a particular Parameter, you can use
the CreateParameter method to make appropriate Parameter objects for a Command
object, which allows you to initialize parameters without any server-side processing.
Otherwise, you must call the Refresh method on the Command object’s Parameters
collection to retrieve parameter information from the server, a resource-intensive
operation.
Recordset and Field
The Recordset and Field objects are the actual data-containing objects in ADO.
A Recordset object represents a set of records retrieved from SQL Server. Because
this is the object that allows you to directly retrieve data, it’s indispensable to ADO
processing. ADO allows you to open a Recordset object directly, or to create one from
a Connection or Command object. As you’ll see later in the chapter, Recordsets have
a variety of properties and behaviors depending on how they’re created.
A Field object represents a single column of data in a Recordset. Once you’ve
retrieved a Recordset, you’ll usually work with the Fields collection to read the data in
the Recordset. However, since the Fields collection is the default property of the
Recordset object, you won’t often see its name in your code. For example, if you’re

example, if you have a Record object representing a file in a file system, its associated
Stream object would represent the binary data in that file.
Because SQL Server is a relational database, it doesn’t support Record or Stream
objects.
Understanding Cursors
You learned about T-SQL cursors in Chapter 8. A cursor, you’ll recall, is a set of records
along with a pointer that identifies one of these records as the current record. ADO
also supports cursors, in the form of the Recordset object. When you open a Recordset
object to contain a set of records, ADO identifies a particular record as the current
record. Thus, if you talk of cursors in an ADO context, you’re normally talking about
Recordsets.
Unlike T-SQL cursors, though, ADO cursors can have a variety of different behav-
iors, depending on the properties you set for the Recordset object. In this section,
we’ll discuss the three key properties that control ADO cursor behavior:
• CursorLocation
• CursorType
• LockType
2627ch19.qxd 8/22/00 11:11 AM Page 728
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
729
CursorLocation
The CursorLocation property can be set to either adUseServer, for server-side cursors,
or adUseClient, for client-side cursors. A cursor is a set of records in memory, and of
course some software has to be responsible for keeping track of this set of records.
Server-side cursors are maintained by SQL Server using the same native cursors that
you met in Chapter 8. Client-side cursors are maintained by the Microsoft Cursor Ser-
vice for OLE DB, which attempts to level the playing field by supplying capabilities
that are lacking in some servers. If no CursorLocation is specified, a server-side cursor
is the default.
Just because SQL Server supports server-side cursors doesn’t mean you have to

730
NOTE The forward-only Recordset is more flexible than you might think at first. In addi-
tion to using the MoveNext method, you can also use the Move method to skip intervening
records, as long as you’re moving forward. A forward-only Recordset also supports the
MoveFirst method, although this seems contradictory. Be aware, though, that this may be
an expensive operation, because it might force the provider to close and reopen the
Recordset.
In general, if you stick to a cursor type that has no more functionality than you
need in your application, you’ll get the best possible performance. If you don’t specify
a cursor type, ADO defaults to the fastest type, which is a forward-only cursor.
LockType
Finally, you can use the LockType parameter to specify the record-locking behavior
that will be used for editing operations. Here again you have four choices:
• adLockReadOnly, for Recordsets that cannot be edited
• adLockPessimistic, for pessimistic locking (record locks are taken for the dura-
tion of all editing operations)
• adLockOptimistic, for optimistic locking (record locks are taken only while data
is being updated)
• adLockBatchOptimistic, for Recordsets that will use the UpdateBatch method to
update multiple records in a single operation
If you don’t specify a lock type, ADO defaults to the fastest type, which is a read-
only Recordset.
WARNING The default Recordset in ADO is server-side, forward-only, and read-only.
If you want to move through records at random or edit records, you must specify the
cursor type and lock type to use.
Graceful Degradation
Just to make things more interesting, what you ask for isn’t always what you get.
Not every provider supports every possible combination of these parameters. In
almost every case, though, you’ll get something close to what you asked for. The
2627ch19.qxd 8/22/00 11:11 AM Page 730

Server-side, static, optimistic Server-side, keyset, optimistic No
Server-side, static, batch optimistic Server-side, keyset, batch optimistic No
Server-side, forward-only, batch
optimistic
Server-side, forward-only, batch
optimistic
UNDERSTANDING CURSORS
Development with
SQL Server
PART
V
2627ch19.qxd 8/22/00 11:11 AM Page 731
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 19 • ADO AND SQL SERVER
732
TABLE 19.1: GRACEFUL DEGRADATION OF RECORDSETS (CONTINUED)
Requested Delivered Identical?
Client-side, forward-only, read-only Client-side, static, read-only No
Client-side, forward-only, pessimistic Client-side, static, batch optimistic No
Client-side, forward-only, optimistic Client-side, static, optimistic No
No
Client-side, keyset, read-only Client-side, static, read-only No
Client-side, keyset, pessimistic Client-side, static, batch optimistic No
Client-side, keyset, optimistic Client-side, static, optimistic No
Client-side, keyset, batch optimistic Client-side, static, batch optimistic No
Client-side, dynamic, read-only Client-side, static, read-only No
Client-side, dynamic, pessimistic Client-side, static, batch optimistic No
Client-side, dynamic, optimistic Client-side, static, optimistic No
Client-side, dynamic, batch optimistic Client-side, static, batch optimistic No
Client-side, static, read-only Client-side, static, read-only Yes

calling the Open method. In this case, you don’t need to set the Connection-
String property in advance.
• The UserID argument specifies the username to use with the data source.
• The Password argument specifies the password to use with the data source.
• The Options argument can be set to adConnectUnspecified (the default) for a
synchronous connection or adAsyncConnect for an asynchronous connection.
Once the connection is made, either type performs the same. The difference is
that an asynchronous connection lets other code in your client application con-
tinue running while the connection is being made.
Of course, to build a connection string, you need to understand from what it’s
made up. The basic syntax of an OLE DB connection string is as follows:
keyword=value;keyword=value;keyword=value…
Table 19.2 shows the keywords that you can use in a SQL Server connection string.
TABLE 19.2: OLE DB CONNECTION STRING KEYWORDS FOR SQL SERVER
Keyword Value Comments
Provider SQLOLEDB Must be specified. This tells OLE DB the
type of database with which you want to
connect.
Data Source Name of the SQL Server Must be specified. You can also use the
special value “(local)” if the SQL Server is
on the computer where the client code
will run.
SAMPLE ADO CODE
Development with
SQL Server
PART
V
2627ch19.qxd 8/22/00 11:11 AM Page 733
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 19 • ADO AND SQL SERVER

Dim conLocal As ADODB.Connection
Set conLocal = New ADODB.Connection
2627ch19.qxd 8/22/00 11:11 AM Page 734
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
735
conLocal.ConnectionString = _
“Provider=SQLOLEDB;Server=(local);” & _
“Database=pubs;Trusted_Connection=Yes”
conLocal.Open
Alternatively, you can save a line of code by including the connection string with
the Open method:
Dim conLocal As ADODB.Connection
Set conLocal = New ADODB.Connection
conLocal.Open _
“Provider=SQLOLEDB;Server=(local);” & _
“Database=pubs;Trusted_Connection=Yes”
It really doesn’t matter which of these formats you use to open a connection; you
should choose the one that makes it easier for you to remember what the code is
doing.
NOTE We’re using Visual Basic for the examples in this chapter. Because ADO is a COM
server, you can use it from any COM-aware language, but we feel that Visual Basic is the
most widely understood and the easiest to read even if you don’t know its precise syntax.
To use ADO in Visual Basic, you need to use the Project ➢ References menu item to set a
reference to the current version of the Microsoft ActiveX Data Objects Library.
Connecting to a SQL Server across the network using a SQL Server user ID and
password is just as simple. For example, to connect with the Northwind database on a
server named BIGREDBARN as a user named test with a password of test, you could
use this code:
Dim conNetwork As ADODB.Connection
Set conNetwork = New ADODB.Connection

the command altered.
The Options argument can either specify how the CommandText should be inter-
preted or supply options for executing it. Some of the values you can supply for
Options are as follows:
• adCmdUnknown (the default) indicates that ADO should figure out for itself
whether the command is a SQL statement or a stored procedure.
• adCmdText indicates that the command is a SQL statement.
• adCmdStoredProc indicates that the command is a stored procedure.
• adAsyncExecute tells ADO to execute the command asynchronously.
• adExecuteNoRecords indicates that the command does not return any rows. You
don’t have to specify this, but it does make the method more efficient to do so.
As a first example, here’s code to execute a SQL statement directly. This particular
statement will create a stored procedure in the local copy of the Northwind database:
Dim conLocal As ADODB.Connection
Dim lngRows As Long
Set conLocal = New ADODB.Connection
2627ch19.qxd 8/22/00 11:11 AM Page 736
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
737
conLocal.Open _
“Provider=SQLOLEDB;Server=(local);” & _
“Database=Northwind;Trusted_Connection=Yes”
conLocal.Execute _
“CREATE PROC NewPrices AS UPDATE Products “ & _
“SET UnitPrice = UnitPrice * 1.1”, lngRows, _
adCmdText + adExecuteNoRecords
Debug.Print lngRows
If you run this code, you’ll find that the lngRows variable is set to –1. That’s ADO’s
way of telling you that the command didn’t return any rows at all. If it had returned
an empty Recordset, lngRows would be set to zero instead.

“NewPrices”
In this case, of course, you won’t get any feedback as to the number of rows
changed by the Execute method.
ADO offers one more interesting syntactical twist. You can treat a named statement
(such as a stored procedure) as a method of the Connection object. An example will
make this more clear:
Dim conLocal As ADODB.Connection
Set conLocal = New ADODB.Connection
conLocal.Open _
“Provider=SQLOLEDB;Server=(local);” & _
“Database=Northwind;Trusted_Connection=Yes”
conLocal.NewPrices
Assuming that there is a stored procedure named NewPrices in the Northwind
database, this bit of code will execute that stored procedure. Once again, there’s no
return value to tell you how many rows were altered.
Using the Command Object
The Command object also has an Execute method with three optional arguments:
Command.Execute RecordsAffected, Parameters, Options
The RecordsAffected argument is a variable (not a constant). If you choose to supply
this argument, it will be filled in by SQL Server with the number of records that the
command altered.
The Parameters argument can be used to hold a variant array of parameters to be
passed to the command being executed on the server.
The Options argument can either specify how the command should be interpreted
or supply options for executing it. Some of the values you can supply for Options are
as follows:
• adCmdUnknown (the default) indicates that ADO should figure out for itself
whether the command is a SQL statement or a stored procedure.
• adCmdText indicates that the command is a SQL statement.
• adCmdStoredProc indicates that the command is a stored procedure.

“SET UnitPrice = UnitPrice/1.1”
cmdProc.Execute lngRows, , _
adCmdText + adExecuteNoRecords
Debug.Print lngRows
Of course, you can also execute a stored procedure via a Command object by set-
ting the CommandText property to the name of the stored procedure:
Dim conLocal As ADODB.Connection
Dim cmdProc As ADODB.Command
SAMPLE ADO CODE
Development with
SQL Server
PART
V
2627ch19.qxd 8/22/00 11:11 AM Page 739
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 19 • ADO AND SQL SERVER
740
Dim lngRows As Long
Set conLocal = New ADODB.Connection
conLocal.Open _
“Provider=SQLOLEDB;Server=(local);” & _
“Database=Northwind;Trusted_Connection=Yes”
Set cmdProc = New ADODB.Command
Set cmdProc.ActiveConnection = conLocal
cmdProc.CommandText = “NewPrices2”
cmdProc.Execute lngRows, , _
adCmdStoredProc + adExecuteNoRecords
Debug.Print lngRows
You can also use the Command’s Name property to assign a name to the Com-
mand. If you do this, you can then treat that command as a method of the corre-

“@factor float AS UPDATE Products “ & _
“SET UnitPrice = UnitPrice * @factor”
cmdProc.Execute lngRows, , _
adCmdText + adExecuteNoRecords
Debug.Print lngRows
The NewPrices3 stored procedure requires a single parameter named @factor of
datatype float to do its job. To supply this parameter, you can work with the Parame-
ters collection of a Command object. One way to do this is to use the Refresh method
of the Parameters collection to get parameter information:
Dim conLocal As ADODB.Connection
Dim cmdProc As ADODB.Command
Dim lngRows As Long
Set conLocal = New ADODB.Connection
conLocal.Open _
“Provider=SQLOLEDB;Server=(local);” & _
“Database=Northwind;Trusted_Connection=Yes”
Set cmdProc = New ADODB.Command
Set cmdProc.ActiveConnection = conLocal
cmdProc.CommandText = “NewPrices3”
cmdProc.CommandType = adCmdStoredProc
cmdProc.Parameters.Refresh
cmdProc.Parameters(1) = 1.1
cmdProc.Execute lngRows, , _
adExecuteNoRecords
Debug.Print lngRows
SAMPLE ADO CODE
Development with
SQL Server
PART
V

cmdProc.Execute lngRows, , _
adExecuteNoRecords
Debug.Print lngRows
To make this technique work, follow these steps:
1. Call the Command.CreateParameter method once for each parameter required
by the stored procedure. Supply the name of the parameter, a constant indicat-
ing the datatype, and another constant indicating whether it’s an input or an
output parameter.
2. Set the Value property of the new parameter.
3. Append the new parameter to the Parameters collection of the Command
object.
2627ch19.qxd 8/22/00 11:11 AM Page 742
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
743
Recordset Operations
Although executing commands is a necessary part of working with SQL Server, more
often you’ll want to work with groups of records—that is, with Recordset objects. In
this section, we’ll show you the basic Recordset operations:
• Opening a Recordset directly from a table
• Opening a Recordset from an unparameterized query
• Opening a Recordset from a parameterized query
• Moving through a Recordset
• Editing records
• Adding records
• Deleting records
• Persisting Recordsets
Opening from a Table
The first method of the Recordset object you’ll need to use is the Open method. As
you might guess, this is the key method for attaching a Recordset object to a cursor of
records:

procedure name
• adAsyncExecute to open the Recordset asynchronously
• adAsyncFetch to fill the record cache asynchronously
In addition to the CursorType and LockType properties, the Recordset object also
has a CursorLocation property that can be set to adUseClient (for client-side cursors)
or adUseServer (for server-side cursors). This property must be set before you call the
Open method; it can’t be supplied as part of the Open method itself.
With that explanation out of the way, let’s look at a couple of examples of opening
Recordsets based directly on tables. Here’s perhaps the simplest possible way to do so:
Dim conLocal As ADODB.Connection
Dim rstCustomers As ADODB.Recordset
Set conLocal = New ADODB.Connection
conLocal.Open _
“Provider=SQLOLEDB;Server=(local);” & _
“Database=Northwind;Trusted_Connection=Yes”
Set rstCustomers = New ADODB.Recordset
rstCustomers.Open “Customers”, conLocal
Debug.Print rstCustomers.RecordCount
This code opens a Recordset holding every row from the Customers table using the
default properties: forward-only, read-only, and server-side. The two absolutely neces-
sary pieces of information (the source and the connection) are supplied directly in the
Open method’s arguments.
If you run this code, you’ll notice that rstCustomers.RecordCount reports –1, even
though there are customers in the database. In general, you can’t depend on the
RecordCount to accurately count the records in a Recordset. Rather, it’s a count of the
records that have been fetched. Because this is a forward-only Recordset, there’s no
way for ADO to pre-fetch (and therefore count) the records, so it returns the special
value –1. You can think of this value as meaning I don’t know how many records there
are here.
2627ch19.qxd 8/22/00 11:11 AM Page 744