Index_S


S

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

BEGINEND 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




SQL Server 2000 Stored Procedures Handbook
SQL Server 2000 Stored Procedures Handbook (Experts Voice)
ISBN: 1590592875
EAN: 2147483647
Year: 2005
Pages: 100

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net