CHAPTER 14 • STORED PROCEDURES
524
create the stored procedure does not change, but when you execute the stored proce-
dure, it looks as follows:
EXEC Show_Authors WITH RECOMPILE
By using these RECOMPILE statements, you can keep your stored procedures running
fast. However, thus far, you haven’t secured them from prying eyes—let’s do that now.
Securing Your Stored Procedures
When you create a stored procedure, you are just creating a query that is stored on
the server rather than on the client machines. These stored procedures are contained
in the syscomments system table in each database and are completely accessible by
default. This means that by executing a simple SELECT query against the syscom-
ments table in the database where the stored procedure was created, your users could
see all of the code used to create the procedure. This may not be desirable because one
of the main uses of a stored procedure is to remove the user from the complexity and
structure of the underlying tables, and, as we will discuss in Chapter 18, stored proce-
dures are used for securing tables as well. By reading the definition of the stored pro-
cedure right from syscomments, the users would be bypassing that security; in other
words, they would be hacking. To avoid that, you should create stored procedures
using the WITH ENCRYPTION statement.
WITH ENCRYPTION is designed to keep prying eyes out of definitions stored in
the syscomments table—not just for stored procedures, but for everything stored
there (views, triggers, etc.). In the following exercise, you will execute a SELECT query
against the syscomments table in the pubs database to see what is stored there and,
therefore, what your users could see:
1. Open Query Analyzer and log in using Windows NT Authentication (unless you
need to use SQL Server Authentication).
2. Enter the following code and execute it by clicking the green arrow button on
the toolbar (you have to join the sysobjects table because the name is stored
there—only the ID is stored in syscomments):
USE Pubs
SELECT ob.name, com.text
FROM syscomments com
JOIN sysobjects ob
ON ob.id = com.id
WHERE ob.name = ‘Show_Authors’
2627ch14.qxt 8/22/00 10:53 AM Page 524
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
525
3. Notice in the result set that you can read the code used to create and run the
stored procedure.
4. To encrypt it, open Enterprise Manager, expand the pubs database, then select
the Stored Procedures icon.
5. In the contents pane, double-click the Show_Authors stored procedure to bring
up the properties.
6. To encrypt the stored procedure, change the definition to look as follows (notice
the bold changes):
CREATE PROCEDURE DBO.Show_Authors
@city varchar(50) = ‘Oakland’
WITH ENCRYPTION
AS
SELECT au_fname, au_lname, address, city, state, zip
FROM authors
WHERE city = @city
ORDER BY au_lname DESC
UNDERSTANDING STORED PROCEDURES
Digging into SQL
Server
PART
III
2627ch14.qxt 8/22/00 10:53 AM Page 525
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 14 • STORED PROCEDURES
526
7. Click OK to apply the changes.
8. To verify that it has been encrypted, double-click Show_Authors to bring up the
properties again. You should receive an error message stating that the object is
encrypted and therefore unreadable. Click OK to return to the stored procedure
properties screen.
9. Return to Query Analyzer and execute the query from step 2 again; notice that
this time you cannot read the text from syscomments, because it is full of
unreadable characters (these characters may vary depending on your system).
2627ch14.qxt 8/22/00 10:53 AM Page 526
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
527
10. Close Query Analyzer.
WARNING Once you create an object, such as a stored procedure, using WITH
ENCRYPTION, you cannot decrypt the object. Make sure you are finished modifying the
object for a while before encrypting.
User-defined stored procedures (the ones you make yourself) are a very powerful tool,
but they are not the only stored procedures with which you have to work. Microsoft has
given you a batch of ready-made stored procedures that are designed to help you work
with system tables. These are called system and extended stored procedures.
Using System and Extended Stored Procedures
Microsoft has started using the term metadata quite a bit these days; it means informa-
tion about information. When the term is applied to SQL Server, it means information
about objects on the server, such as how big a database file is or what permissions a
user has. When you want to change or read such system information, you could open
the system tables directly and start fiddling with the data inside, but that usually turns
UNDERSTANDING STORED PROCEDURES
Digging into SQL
Server
PART
III
2627ch14.qxt 8/22/00 10:53 AM Page 527
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 14 • STORED PROCEDURES
528
out badly because most of the values in the system tables are not designed to be under-
stood by mere mortal humans (most of the values in these tables are numeric and not
easily decoded). A much better way, the supported way, to change or read the system
information is by using system stored procedures.
Using System Stored Procedures
Every time you add a database, add a login (which is used to grant access to SQL Server),
create an index, or add or modify any object on the server, you are making changes to
the system tables, which is where SQL Server stores information about your objects. The
information stored in these system tables is mostly numeric data, which is difficult to
read, let alone modify, directly. That is why Microsoft has given you scores of stored
procedures (about 650) to help with the task of modifying system tables. They are all
stored in the master and msdb databases, and most begin with the characters sp_. Here
is a synopsis of some of the more common system stored procedures:
sp_tables: This stored procedure will show you any object that can be used
in the FROM clause of a SELECT query. This is useful if you have forgotten or
just don’t know the exact name of the table or view you need to query.
sp_stored_procedures: This will list all of the stored procedures available
for your use. Again this is useful if you have forgotten or just don’t know the
name of the procedure you need.
sp_server_info: Using this procedure is the best way to determine how your
SQL Server was configured at setup, such as the character set or sort order that
was defined at install, what version of SQL Server you are running (for exam-
ple, desktop or standard), etc.
sp_databases: This lists all of the available databases on the server. It can be
useful for finding database names.
sp_start_job: This is used to start an automation job in SQL Server. This is
very handy for jobs that are scheduled on demand. We’ll be discussing jobs and
automation in Chapter 17.
sp_stop_job: This procedure will stop a job that has been started already.
sp_addlogin: This procedure is used to add a standard login to the server to
allow users access to the server as a whole. This is very useful for creating a
script that will regenerate user logins in the event of a system crash. We’ll dis-
cuss security and logins in Chapter 18.
sp_grantlogin: This is used to grant access on SQL Server to a Windows NT
account. This should be combined with the sp_addlogin account to create a
script to re-create user accounts in the event of a disaster.
2627ch14.qxt 8/22/00 10:53 AM Page 528
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
529
sp_setapprole: An account role in SQL Server (as you will see in Chapter 18)
is used to make sure that only approved applications are used to access your
database. This stored procedure activates the application role so that the user
can access the database with the permissions that are granted to the applica-
tion role.
sp_password: As you will see in Chapter 18, there is a difference between
standard and Windows NT login accounts; this stored procedure is used to
change passwords for standard, and only standard, logins.
sp_configure: Several global configuration options can be set to change the
way SQL Server behaves. For example, you can tell the server whether to allow
updates to system tables directly or how much system memory to use. The
sp_configure stored procedure can be used to change such options. The avail-
able options are listed here:
• affinity mask
• allow updates
• concat_null_yields_null
• cost threshold for parallelism
• cursor threshold
• default full-text language
• default language
• extended memory size
• fill factor
• index create memory
• language in cache
• lightweight pooling
• locks
• max degree of parallelism
• max server memory
• max text repl size
• max worker threads
• media retention
• min memory per query
• min server memory
• nested triggers
UNDERSTANDING STORED PROCEDURES
Digging into SQL
Server
PART
III
2627ch14.qxt 8/22/00 10:53 AM Page 529
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 14 • STORED PROCEDURES
530
• network packet size
• numeric_roundabort
• open objects
• priority boost
• query governor cost limit
• query wait
• recovery interval
• remote access
• remote login timeout
• remote proc trans
• remote query timeout
• resource timeout
• scan for startup procs
• set working set size
• show advanced options
• spin counter
• time slice
• two digit year cutoff
• user connections
• user options
sp_attach_db: All of the databases on your SQL Server have a record in the
sysdatabases system table in the master database. This record tells SQL Server
where the database is on disk, how big it is, etc. If you were to lose your master
database and (heaven forbid) not have a good backup, you would need to run
this stored procedure to re-create the records in sysdatabases for each of the
databases on your server.
sp_processmail: SQL Server is capable of not only sending, but receiving
and responding to e-mail. When SQL Mail is configured (which you will learn
how to do in Chapter 17), you can send a query via e-mail to the MSSQLServer
service. When you run this stored procedure, the MSSQLServer service will read
the query in the e-mail and send back the result set.
2627ch14.qxt 8/22/00 10:53 AM Page 530
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
531
sp_monitor: This stored procedure gives a quick snapshot of how your
server is doing—i.e., how busy the processor is, how much RAM is in use, etc.
sp_who: You cannot perform some administrative tasks, such as renaming or
restoring a database, if someone is using it at the time. To find out who is using
a database on the server so that you can disconnect them, use the sp_who
stored procedure.
sp_rename: This will change the name of any object in the database.
sp_renamedb: This will change the name of the database itself.
sp_help: This can be used to find information about any object in the data-
base. It returns properties such as created date, column names, foreign-key con-
straints, etc.
sp_helptext: This is used to display the actual text that was used to create
an object in the database. This information is read from the syscomments
table.
sp_help*: There are many other stored procedures that have sp_help as the
first few characters. All of them are designed to give you specific information
about a type of object in the database.
These system stored procedures are used like any other stored procedure. Let’s look
at an example:
1. Open Query Analyzer from the SQL Server 2000 group under Programs on the
Start menu and log in with Windows NT Authentication (unless you must use
SQL Server Authentication).
2. To use sp_help to get information about the authors table in the pubs database,
enter and execute the following code:
USE Pubs
EXEC sp_help ‘authors’
UNDERSTANDING STORED PROCEDURES
Digging into SQL
Server
PART
III
2627ch14.qxt 8/22/00 10:53 AM Page 531
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 14 • STORED PROCEDURES
532
3. To see how your SQL Server is faring at the moment, use the sp_monitor stored
procedure:
EXEC sp_monitor
4. Close Query Analyzer.
2627ch14.qxt 8/22/00 10:53 AM Page 532
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
533
Using Extended Stored Procedures
Another type of stored procedure is the extended stored procedure. These do just what
the name implies: They extend the capabilities of SQL Server so that it can do things
that a database server would not ordinarily be capable of doing. For example, you
wouldn’t expect a database server to be able to execute a command from the com-
mand prompt, but thanks to an extended stored procedure that comes with SQL
Server, called xp_cmdshell, SQL Server can do just that.
Extended stored procedures are just C++ code saved in and executed from a
Dynamic Link Library (DLL). Most of the extended stored procedures are executed
with other system stored procedures, so you won’t use them very often by them-
selves, but here is a short list of the ones you may use:
xp_cmdshell: This stored procedure is used to run programs that are ordi-
narily run from the command shell, such as the dir command or md (make
directory). This comes in very handy when you need to have SQL Server create
a directory for automatically archiving BCP files or something of that nature.
xp_fileexist: This procedure can be used to test for the existence of a file
and, if that file exists, to do something (such as BCP) with it. The following
code shows you how to test for the existence of the autoexec.bat file. If @ret =
1, the file exists; if it equals 0, the file does not exist. This is not documented in
Books Online or on the Microsoft Web site, so we will give you the syntax here.
The second line declares a variable to hold an output parameter, the third line
calls the procedure with an output parameter, and the fourth line displays the
output (note that this must be done in the master database):
USE Master
DECLARE @ret int
EXEC xp_fileexist ‘c:\autoexec.bat’, @ret output
SELECT @ret
xp_fixeddrives: This shows you the drive letters of the fixed disks and how
many MBs of available space are on each one.
Again, each of these extended stored procedures is executed just like a regular
stored procedure. Let’s try some here:
1. Open Query Analyzer from the MS SQL Server group under Programs on the
Start menu and log in with Windows NT Authentication.
2. To use xp_cmdshell to get a directory listing of your C drive, enter and execute
the following code:
EXEC xp_cmdshell ‘dir c:’
UNDERSTANDING STORED PROCEDURES
Digging into SQL
Server
PART
III
2627ch14.qxt 8/22/00 10:53 AM Page 533
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét