sa password, 223
auditing use, 223
generating random password, 223
savepoints
ROLLBACK TRANSACTION command, 90
transactions, 86
stored procedures, 90
scalar valued functions, 186
description, 186
in-built functions, 186
user defined functions, 186
constraints, 187
scan for startup procs config option
sp_configure system stored procedure, 16
schema binding
deterministic functions, 194
requirements, 193
user defined functions, 193
WITH SCHEMABINDING statement, 193
schema locks, 94
scope for cursor, 67
ALTER DATABASE command, 67
DATABASEPROPERTYEX() function, 68
GLOBAL scope, 67
LOCAL scope, 67
SCROLL
direction for cursor, 68
SCROLL_LOCKS
updatability of cursor, 69
security, 219
0x80000000 status bit security issues, 139
data encryption, 242
need for security, 219
SQL Server vulnerabilities, 220
mis-configuration, 220
SQL buffer overflow, 243
SQL injection attacks, 234
SSL, 244
stored procedures, 51, 219
data validation, 51
dynamic SQL in stored procedures, 50
encrypted stored procedures, 239
improving database security, 121
SERIALIZABLE
isolation levels, 13, 98
serializable procedure execution article
stored procedures and replication, 13
Server Network Utility
changing server settings, 222
SET NOCOUNT ON option
example of using, 37
high performance stored procedures, 119
optimizing stored procedures, 35
SET SHOWPLAN_TEXT command
textual view of execution plan, 40
set-based operations
compared to cursors, 66
SetCert tool, 244
using SSL encryption, 245
setup problems
permissions issues, 223
security holes, 221
SQL Server security vulnerabilities, 220
shared locks, 94
Show Execution Plan option
graphical view of execution plan, 39
simple model
recovery model for database, 81
simple transactions, 82
BEGIN TRANSACTION command, 82
COMMIT TRANSACTION command, 82
distributed transactions, 87
nesting transactions, 84
ROLLBACK TRANSACTION command, 82
syntax, 82
single row of data
returning values from stored procedures, 30
single statement table function
example, 188
TABLE data type, 188
snapshot replication, 214
source control systems
stored procedures, 18
Visual SourceSafe, 18
sp_ prefix
avoiding in naming stored procedures, 17
creating global tables and views, 144
extended stored procedures, 136
system stored procedures, 7, 136
sp_addlogin system stored procedure, 147
sp_addrole system stored procedure, 148, 233
sp_addrolemember system stored procedure, 148, 227
sp_adduser system stored procedure, 148, 233
sp_attach_db system stored procedure, 146
sp_change_users_login system stored procedure, 146
sp_changedbowner system stored procedure, 146
sp_changeobjectowner system stored procedure, 228
sp_configure system stored procedure, 145, 195
scan for startup procs config option, 16
sp_cycle_errorlog system stored procedure, 148
exanple of using, 166
sp_dboption system stored procedure, 145
sp_depends system stored procedure, 193
renaming stored procedures, 19
sp_detach_db system stored procedure, 146
sp_executesql system stored procedure, 149
Parameters collection, 237, 238
sp_getsid stored procedure
get_sid function, 143
sp_grantdbaccess system stored procedure, 148
sp_grantlogin system stored procedure, 147, 232
sp_help system stored procedure, 147
sp_helpdb system stored procedure, 147
sp_helprole system stored procedure, 227
sp_helpserver system stored procedure, 147
sp_helptext system stored procedure, 147
sp_instance_regread system stored procedure
using, 166
sp_lock system stored procedure, 145
sp_password system stored procedure, 145
sp_procoption system stored procedure, 147
running stored procedure on start up, 16, 127, 128
using, 166
sp_recompile system stored procedure
recompiling stored procedures, 20
sp_rename system stored procedure, 146
renaming stored procedures, 19
sp_spaceused system stored procedure, 146
sp_start_job system stored procedure, 150
sp_stop_job system stored procedure, 150
sp_trace extended stored procedures
tracing object creation and deletion, 169
sp_trace_setevent extended stored procedure
executing code at start up, 128
sp_trace_setstatus extended stored procedure
executing code at start up, 128
sp_validatelogins system stored procedure, 150
sp_who2 system stored procedure, 145
sp_xml_preparedocument system stored procedure, 148
sp_xml_removedocument system stored procedure, 148
special cursor syntax, 67
cursor source, 69
data updatability for cursor, 69
direction for cursor, 68
scope for cursor, 67
type of cursor, 68
updatability of cursor, 68
warnings for cursor, 69
SQL buffer overflow
DBCC statements, 243
PWDENCRYPT() function, 243
removing stored procedures to prevent buffer overflow, 243
Merge Replication stored procedures, 243
Meta Data Services stored procedures, 243
SQL Server security vulnerabilities, 243
SQL injection attacks
deleting database table, 236
examples, 234
permissions issues, 239
problems with, 234
PUBLIC role, 239
solutions, 237
Parameters collection, 237
REPLACE() function, 237
SQL Server security vulnerabilities, 234
SQL Mail
guidelines for using, 170
Outlook and, 170
system stored procedures, 169
XP_SendMail system stored procedure, 169
bullet-proofing XP_SendMail, 172
recursion, 170
XP_SMTP_SendMail stored procedure, 172
SQL Server Agent
flow control of stored procedures, 26
SQL Server concurrency controls, 93
isolation levels, 96
locks, 93
SQL Server Debugger, 57
debug options, 60
Auto Rollback option, 60
Go option, 60
Remove All Breakpoints option, 60
Restart option, 60
Run to Cursor option, 60
Step Into option, 60
Step Out option, 60
Step Over option, 60
Stop Debugging option, 60
Toggle Breakpoint option, 60
debugging stored procedure, 58
permissions issues, 58
Query Analyzer, 57
SQL Server security vulnerabilities, 220
mis-configuration, 220
permissions issues, 223
setup problems, 220
SQL buffer overflow, 243
SqlCommand class
CommandText property, 117
Connection property, 117
ExecuteReader method, 117
SqlConnection class
Open method, 117
SSL, 244
CA, 244
installing SSL certificate, 244
SetCert tool, 244
using SSL encryption, 245
statement permissions
permissions issues, 226
STATIC
type of cursor, 68
status column
0x80000000 status bit, 137
system stored procedures, 136
Step Into option
SQL Server Debugger, 60
Step Out option
SQL Server Debugger, 60
Step Over option
SQL Server Debugger, 60
Stop Debugging option
SQL Server Debugger, 60
stored procedures, 5
see also system stored procedures.
advantages, 6
bad practice, 129
batch operations, 78
calling stored procedures, 17
performance issues, 17
commenting stored procedures, 18
compared to CHECK constraints, 17, 124
compared to extended stored procedures, 136
compared to triggers, 126, 213
compared to T-SQL code
data validation, 122
database security, 122
performance, 115, 118
compared to user interface data validation, 125
compared to user defined functions, 179, 194
creating stored procedures, 9
CREATE PROCEDURE statement, 9
deferred name resolution, 10
storing stored procedure, 10
debugging, 57
SQL Server Debugger, 57, 58
definition, 6
design principles, 13
dynamic SQL in stored procedures, 50
error handling, 52
extended stored procedures, 6
flow control, 21
BEGIN…END code blocks, 21
decisions, 22
loops, 25
pausing and halting execution, 26
generic stored procedures, 131
grouping stored procedures, 129
introduction, 5
modifying stored procedures, 19
ALTER PROCEDURE statement, 19
multiple statement stored procedures, 43
naming stored procedures, 17
nested stored procedures, 27, 131
networking, 11
optimizing stored procedures, 35
execution plan, 38
parameters, 18, 20, 131
avoiding too many parameters, 132
matching input and ouput parameters, 31
performance gain, 11
cached query plans, 11
reasons for using, 11, 111
data validation, 122
encapsulating logic in stored procedures, 112
executing code at start up, 127
improving database security, 121
improving performance, 115
simplifying administration, 120
simplifying deployment, 120
recompiling stored procedures, 19
recycled procedures, 15
removing stored procedures to prevent
buffer overflow, 243
replication, 12
procedure execution article, 13
returning values, 28
error handling, 30
OUTPUT method, 29
RETURN method, 28
single row of data, 30
running on start up, 16, 127
security, 51, 219
data validation, 51
encrypted stored procedures, 239
improving database security, 121
source control systems, 18
strategy for data encryption, 242
system stored procedures, 6, 135
transactions, 88
savepoints, 90
triggers, 9, 198
user defined functions, 9
user stored procedures, 8
SUBSTRING() function
COLUMNS_UPDATED test, 206
syscomments table
database objects, 225
encrypted stored procedures, 240
storage of stored procedure, 240
storing stored procedure, 10
sysdepends table
storing stored procedure, 10
sysmembers table
roles, 227
user ID's, 227
sysobjects table
database objects, 225
encrypted stored procedures, 240
storing stored procedure, 10
system stored procedures, 6, 135
categories of system stored procedures, 149
checking data integrity, 15
DBCC statements, 15
complex tasks, performing, 166
creating system stored procedures, 7, 140
0x80000000 status bit, 140
care in using, 140
documentation, 141
example, 141
naming guidelines, 140
definition, 136
effects of setting 0x80000000 status bit, 138
Enterprise Manager, 7
extended stored procedures, 136
undocumented features, 152
GLOBAL scope, 136
introduction, 6, 135
limited documentation, 135
list of important documented system stored procedures, 145
mining system stored procedures, 150
Query Analyzer, 6
sp_ prefix, 7, 136
SQL Mail, 169
status column, 136
0x80000000 status bit, 137
tracing object creation and deletion, 168
undocumented features, 137, 142, 151
extended stored procedures, 152
file system stored procedures, 161
get_sid function, 143
Windows registry system stored procedures, 152
xp_ prefix, 136
sysusers table
roles, 227
user ID's, 227
sysxlogins table
login ID's, 224
password column, 224