Exploiting MS-SQL Server Built-in Stored Procedures

Using sp_detach_db to Remove a Database From and sp_attach_db to Add a Database to an MS SQL Server

To remove a database from an MS-SQL Server-while leaving the database data (.mdf) and transaction log (.ldf) files intact on the hard drive-call the built-in stored procedure sp_detach_db. Although you cannot access the data within a database while it is not attached to an MS-SQL Server, you can copy or move the database .mdf file (which contains the database data and all its objects) onto another hard drive or onto another server altogether. Then, as we will discuss later within this tip, you can reattach the database data file (and optionally, its transaction log file) onto the same or a different MS-SQL Server. When that is completed, you can once again work with the data and objects within the database.

The built-in stored procedure sp_detach_db, which allows you to remove a database from the MS-SQL Server without deleting the database's files from the hard drive, has the following syntax

 sp_detach_db [@dbname=]''
 [, [@skipchecks=]{'TRUE'|'FALSE'}]

where:

• @dbname is the name of the database you want to remove from the MS-SQL Server.
• @skipchecks specifies whether the MS-SQL Server is to run the Transact-SQL UPDATE STATISTICS statement on each table and indexed view within the database before detaching the database from the DBMS. If FALSE or NULL, the DBMS runs UPDATE STATISTICS; if TRUE (which is the default if you don't specify a value for @SKIPCHECKS), the DBMS does not run UPDATE STATISTICS.

Note that the MS-SQL Server keeps statistics about the distribution of key values within an index and (sometimes) the same statistics about values stored within some of the table's non-indexed columns. The optimizer uses these statistics to determine which index or table column to use when executing a query. Whenever there is a significant change to the values stored within a table, or if someone added a large amount of data (with a bulk INSERT INTO) or removed a lot of data (with a TRUNCATE), you should set @SKIPCHECKS to FALSE so the DBMS will update table and index statistics. In addition, if you plan to move the DBMS to a read-only device, set @SKIPCHECKS to FALSE, so that permanent database has the most up-to-date indexes, which will allow queries to retrieve data as efficiently and quickly as possible. Otherwise, set @SKIPCHECKS TRUE or omit its value from the stored procedure call.

To remove the database SQLTips from an MS-SQL Server without updating statistics on INDEXES and table column values, submit the following EXEC statement to the DBMS:

 EXEC sp_detach_db @dbname='SQLTips', @skipchecks='TRUE'

When you must reattach to an MS-SQL Server a database (.mdf) file that you previously detached with sp_detach_db, use the built-in stored procedure call sp_attach_db, whose syntax is shown here

 sp_attach_db [@dbname=]''
 ,[@filename<n>=]
 ''
 [...,@filename16]

where:

• @dbname is the name of the database you want to attach to an MS-SQL Server. You do not have to use the same name the database had when it was previously attached to an MS-SQL Server; any valid database name will do.
• @filename<n> is the full pathname of the database (.mdf) or transaction log (.ldf) file. Whereas an .ldf file holds the database transaction log, the .mdf file holds all database data, objects, user, and role information.

Suppose, for example, that you want to reattach the SQLTips database whose data is stored within the file C:MSSQLDataSQLTips_data.mdf and whose transaction log is stored within the file in D:MSSQLLogFilesSQLTips_log.ldf. To reattach SQLTips to an MS-SQL Server, you would execute the following EXEC statement:

 EXEC sp_attach_db @dbname='MySQLTips',
 @filenamel='C:MSSQLDataSqlTips_data.mdf',
 @filename2='D:MSSQLLogFilesSQLTips_log.ldf'

If you have only the database data (.mdf) file, you would call sp_attach_db as follows, instead:

 EXEC sp_attach_db @dbname='MySQLTips',
 @filename1='C:MSSQLDataSqlTips_data.mdf'

When you have only the database .mdf (data), the stored procedure will attach the data file to the MS-SQL Server and then create a new transaction log.

When you reattach a database to an MS-SQL Server, the database will again be accessible to DBMS users who were previously granted permission to use the database. All database objects and data, as well as user and role definitions are stored within a database .mdf file. As such, after executing the sp_attach_db procedure, the objects, data, logins, and security created within the original, previously detached database will be available within the database on the MS-SQL Server to which it was reattached.

Adding and Removing User Defined Data Types with the MS SQL Server Stored Procedures sp_addtype and sp_droptype

While creating a table, you must assign each column a data type that defines the type of data the column can hold. For example, if a column is of type INTEGER, users and applications can store only whole numbers within the column-characters and numbers with a decimal point are not allowed within the column. Similarly, when you define a column as being of type CHAR(10), the column can hold up to 10 characters, symbols, or numeric digits.

By creating a user-defined data, you can assign a descriptive name to one of the standard SQL data types. The name you assign should describe for the user the type of data and/or range of data values a user will find within a column. Suppose, for example, that you are working with the SALARY column in an EMPLOYEE table. You could define the column's data type as NUMERIC(10,2), or you could use a more descriptive user-defined data type such as EXECUTIVE_SALARY, MANAGER_SALARY, or SUPERVISOR_SALARY. Moreover, by creating a rule and binding it to the user-defined data type assigned to a column, you can ensure that numbers entered into the column (SALARY, in this example) fall within a certain range of values.

Tip 53 "Using the MS-SQL Server Enterprise Manager to Create a User-Defined Data Type," showed you how to create user-defined data types. In addition to creating data types within the Enterprise Manager, MS-SQL Server lets you create data types from the command line by calling the built-in stored procedure sp_addtype, which has the following syntax

 sp_addtype [@typename=]''
 ,[@phystype=]''
 [,[@nulltype=]'{NULL|NOT NULL|NONULL}'
 [,[@owner=]'']

where:

• @typename is the name that describes the type of data or range of values a table column can hold. For example, you might use names like HOURLY_PAYRATE, EXECUTIVE_SALARY, SSAN, and so on for your user-defined data types.
• @phystype is a valid built-in data type on your DBMS product. Most DBMS products support all the data types defined within the SQL specification and add a few of their own. Therefore, check your system documentation for a complete list of pre-defined data types you can assign to the @PHYSTYPE parameter.
• @nulltype specifies whether a column defined as being of the user-defined data type can hold NULL values. You can override this default nullability setting by providing a different setting when you use the user-defined type in a CREATE TABLE or ALTER TABLE statement.
• @owner specifies the username that owns the data type being created. If you do not pass a username to the stored procedure through the @OWNER parameter, the user executing the sp_addtype stored procedure will be the new data type's owner.

For example, to create the user-defined data type SALES_TAX, which defines a numeric value with a maximum of six digits, five of which might follow the decimal point, you would call sp_addtype as shown here:

 EXEC sp_addtype @typename='SALES_TAX',
 @phystype='NUMERIC(6,5),@owner='dbo'

Note that the sp_addtype stored procedure call in this example makes the DBO the user-defined data type's owner. Whenever you make the DBO a user-defined data type's owner, all users can refer to the new data type by name. If a user other than DBO owns a data type, the user must provide both the owner's username and user-defined data type's name when using the data type within a column definition.

When you no longer need a data type, you can drop it from the database using the sp_droptype stored procedure, with the following syntax

 sp_droptype [@typename=]''

where:

• @typename is the name of the user-defined data type you want to delete.

  Note 

  You can only drop (that is, delete) user-defined types not currently in use. Thus, before you can drop a user-defined type, you must remove it from all table definitions in which it is applied to a column. In addition, you must unbind any rules or defaults (using sp_unbindrule or sp_unbindrule fault) you previously bound to the data type.

Thus, to drop the user-defined data type "SSAN" from the DBMS use:

EXEC sp_droptype @typename='SSAN'

Using sp_help to Display Database Object Properties

The built-in stored procedure sp_help lets you display the properties of objects within a database. Just as Windows displays an object's description after you right-click the object and select Properties from the pop-up menu, sp_help returns a description of the object whose name you pass as a parameter to the stored procedure.

The syntax of the sp_help stored procedure call is

 sp_help [[@objname=]]

where:

• @objname is the name of the database object whose properties you want the DBMS to describe. @OBJNAME can be any database object including a user-defined data type, or the name of a table, index, constraint, view, stored procedure, and so on.

If you call sp_help without supplying the name of an object (within the @OBJNAME parameter) as shown in the following code line, the MS-SQL Server will return a results set that lists the name, owner, and data type of each object within the database:

 EXEC sp_help

Therefore, calling sp_help without supplying an object's name is a convenient way to get a list of all database objects. You can then pass the names of objects, one at a time, to sp_help and get additional information on the objects you want.

The results set (that is, the specific properties information) that the DBMS returns when you call sp_help, depends on the type of object you pass to the stored procedure through its @OBJNAME parameter. For example, if you pass the name of a constraint, the DBMS will return a table listing:

• CONSTRAINT_TYPE-The constraint's type.
• CONSTRAINT_NAME-The constraint's name.
• DELETE_ACTION-Either Cascade or No Action for a FOREIGN KEY constraint. Not applicable for all other constraints. DELETE_ACTION is Cascade only when a FOREIGN KEY definition has an ON DELETE CASCADE option.
• UPDATE_ACTION-Either Cascade or No Action for a FOREIGN KEY constraint. Not applicable for all other constraints. UPDATE_ACTION is Cascade only when a FOREIGN KEY definition has an ON UPDATE CASCADE option.
• STATUS_ENABLED-Indicates whether a FOREIGN KEY or CHECK constraint is enabled. Not applicable for all other constraint types.
• STATUS_FOR_REPLICATION-Indicates whether a FOREIGN KEY or CHECK constraint is to be enforced during replication Not applicable for all other constraint types.
• CONSTRAINT_KEYS-The names of the columns that make up the constraint, or for defaults, rules, and check constraints, the text that defines the constraint.

Similarly, to display information about a stored procedure such as USP_PROCESS_CHECK, for example, you would call sp_help as follows:

 EXEC sp_help 'USP_PROCESS_CHECK'

MS-SQL Server will display the name of the stored procedure, the username of its owner, and the date and time it was created. Next, the DBMS will return a results set with information about each of the stored procedure's parameters. The results set includes:

• PARAMETER_NAME-The parameter variable's name, such as @account_number, @check_number, @check_date, and so on.
• TYPE-The parameter's data type.
• LENGTH-The parameter's maximum physical storage size in bytes.
• PREC-The total number of digits (for numeric parameters) or number of characters (for non-numeric parameters).
• SCALE-For numeric parameters, the number of digits allowed to the right of the decimal point, otherwise NULL.
• PARAM_ORDER-The ordinal position of the parameter, that is 1 for the first parameter, 2 for the second, 3 for the third, and so on.

Using sp_helptext to Display the Text That Defines a Stored Procedure, User Defined Function, Trigger, Default, Rule, or View

MS-SQL Server stores the batch of statements you enter to define a stored procedure, user-defined function, trigger, default, rule, or view within the TEXT column of a row within the SYSCOMMENTS table. If you did not encrypt the stored procedure, function, or trigger when you created it, you can display its statements by calling the stored procedure sp_helptext using the following syntax

 sp_helptext [[@objname=]]

where:

@objname is the name of the stored procedure, user-defined data type, function, trigger, default, rule or view.

You can only use sp_helptext to display the statement batch for objects within the current database. For example, the built-in stored procedure sp_helptext is defined within the MASTER database. As such, to display the definition of the stored procedure sp_helptext, you must first execute a "USE master" statement and then you call the stored procedure sp_helptext as shown here:

 EXEC sp_helptext 'sp_helptext'

If you encrypt an object's definition by including the WITH ENCRYPTION clause with the CREATE statement that you executed to create the object, sp_helptext will not be able to display the object's text. Instead, sp_helptext will display the message "The object has been encrypted." Sp_helptext will, of course, display the object's name in place for .

Using sp_depends to Display the Tables and or Views That Define a View

Before altering tables or views, especially when changing the number or order of columns in either type of object, be sure to call sp_depends. As you learned in Tip 11 "Understanding Views," a view is a virtual table that derives its columns and its rows of data either from other views or from base tables within the database. Therefore, if you drop a table, views that reference column values within the table will no longer work. When a user queries a view whose underlying table (or view) has been deleted, the DBMS returns error messages such as the following:

Server: Msg 208, State 1, Procedure vw_show_high_rollers,
Line2

Invalid object name 'high_rollers'.

Server: Msg 4413, Level 16, State 1, Line 1

Could not use view or function 'vw_show_high_rollers'
because of binding errors

While the actual text of the error messages will vary, their point is the same: Views whose base tables (or base views) have been deleted stop displaying data. Moreover, views that stop working may cause a cascade of errors throughout the DBMS as stored procedures and user-defined functions may stop working because the views that feed data to them no longer work. To prevent such data outages, you must remove dependencies before dropping tables or views upon which other views are dependant.

When database object A references a column within database object B, object A is said to be dependent on object B. A view, for example, is dependant on a table when a column within the view references a column within the table. Similarly, a view is dependant upon another view when one of the columns within the first view references a column within the second.

MS-SQL Server makes a note of all dependencies between database objects within the SYSDEPENDS table. Unfortunately, SYSDEPENDS refers to all database objects by ID number rather than by name. As a result, checking which objects are dependant by querying SYSDEPENDS can be challenging. Fortunately, you can use the built-in stored procedure sp_depends to check the dependencies between and among objects by name.

The syntax for the sp_depends stored procedure call is

 sp_depends [[@objname=]]

where:

@objname is the name of the view, table, or other database object whose dependencies you want to examine.

Sp_depends reports not only those objects that depend on the object whose name you pass to the stored procedure (through the @OBJNAME parameter), but also the database objects on which the object itself depends. Thus, before dropping or altering the view VW_HIGH_ROLLERS, for example, call sp_depends (as shown by the following code) to determine if there are any database views that reference columns within the VW_HIGH_ROLLERS view:

 sp_depends 'vw_high_rollers'

If sp_depends reports there are database objects dependant on the view you want to delete, don't delete it. Or, if you delete the view anyway, either change the references within dependant objects, such that they refer to other database objects that have the same data, or delete the dependant objects views as well, because they will no longer work. Of course, before you delete any dependant objects, you should use sp_depends to see if other objects depend on them as well.

Using sp_helpconstraint to Display Information on Table Constraints

A constraint, as you learned in Tip 15 "Understanding Constraints," is a database object that restricts the range of values and type of data a user or application can place within a table column. There are seven types of constraints: assertions, domains, check constraints, foreign key constraints, primary key constraints, required data, and uniqueness constraints. Tip 15 explains the role that each type of constraint plays in maintaining database integrity. The bottom line is that the DBMS prevents users and applications from inserting rows with data that violates any constraints on the table's column or on the table as a whole.

If you are inserting rows manually (by executing INSERT statements through the SQL Query Analyzer, for example), the MS-SQL Server will report any constraint violations onscreen if it rejects the rows you are trying to insert. When you use an external application to insert data into a database table or when you call on a stored procedure to do so, the DBMS still rejects rows with illegal values. However, if the application or stored procedure does not handle errors properly, you might never see the system's error messages—data that you think was inserted into the database will simply go missing.

When writing batch routines that insert or update table data, it is critical that you understand the table's column constraints. Therefore, before you write stored procedures or external applications that update the database, call the built-in stored procedure sp_ helpconstraint to get a list of all constraints to which column values in new rows must adhere. Then, include within your programs and stored procedures code that ensures that data to be inserted does not violate these constraints.

The syntax of an sp_helpconstraint stored procedure call is

 sp_helpconstraint [@objname=]''
 [,[@ncmsg=}{'nomsg'}]

where:

• @objname is the name of the table for which you want the stored procedure to list constraint information.
• @nomsg specifies whether sp_helpconstraint is to display the name of the table whose list of constraints the stored procedure is reporting. Set @NOMSG to nomsg if you want to suppress the table name display, or omit the parameter to display the name of the table along with the table's list of constraints.

For each table constraint, sp_helpconstraint returns a results set that includes:

• CONSTRAINT_TYPE—The type of constraint (CHECK, DEFAULT, PRIMARY KEY, FOREIGN KEY, and so on) and the columns to which the constraint applies.
• CONSTRAINT_NAME—The constraint's unique user- or system-supplied name. User-supplied constraint names are often descriptive of the constraint's purpose. System-supplied names of the table name, followed by the names of the columns to which the constraint applies, and end with a randomly generated sequence of letters and numbers to guarantee the uniqueness of the constraint's name.
• DELETE_ACTION—Either Cascade or No Action for a FOREIGN KEY constraint and not applicable for all other constraints. DELETE_ACTION is Cascade only when a FOREIGN KEY definition has an ON DELETE CASCADE rule.
• UPDATE_ACTION—Either Cascade or No Action for a FOREIGN KEY constraint and not applicable for all other constraints. (UPDATE_ACTION is Cascade only when a FOREIGN KEY definition has an ON UPDATE CASCADE rule.)
• STATUS_ENABLED—Indicates whether a FOREIGN KEY or CHECK constraint is enabled and not applicable for all other constraint types.
• STATUS_FOR_REPLICATION—Indicates whether a FOREIGN KEY or CHECK constraint is to be enforced during replication and not applicable for all other types of constraints.

In addition to the preceding information about column constraints, sp_helpconstraint also lists any FOREIGN KEY constraints that reference the table—giving the name of each FOREIGN KEY and the name of the table in which the FOREIGN KEY is defined.

For example, to display the constraints on the AUTHORS table within the PUBS database you would execute the following statement batch:

 USE PUBS
 EXEC sp_helpconstraint 'authors'

Note 

You must call sp_helpconstraint from within the database that has the table for which you want the stored procedure to report constraints. In this example, PUBS must be the current database for the sp_helpconstraint procedure to report the constraints on the AUTHORS table as shown in Figure 566.1.

Figure 566.1: Constraint information returned by the stored procedure sp_helpconstraint about the AUTHORS table

To display the text of CHECK and DEFAULT constraints, use the built-in stored procedure sp_helptext (which you learned about in Tip 564 "Using the MS-SQL Server Stored Procedure sp_helptext to Display the Text that Defines a Stored Procedure, User-Defined Function, Trigger, Default, Rule, or View"). In this example, you would execute the following EXEC command to display the text of the CHECK constraint on the AU_ID column within the AUTHORS table:

 EXEC sp_helptext 'CK__authors__au_id__77BFCB91

When passing a system-supplied constraint name to the sp_helptext stored procedure, be sure to note the two underscores that precede the table name (AUTHORS), the column name (AU_ID), and the random string ("77BFCB91") within the constraint.

Using sp_pkeys to Display Information on a Table s PRIMARY KEY

You can establish a parent/child relationship between any two tables by making a FOREIGN KEY reference within the child table to the PRIMARY KEY within the parent table. A PRIMARY KEY is nothing more than one or more columns within a table to which the PRIMARY KEY constraint has been applied. Similarly, a FOREIGN KEY is a column or set of columns that have a FOREIGN KEY constraint. The importance of the PRIMARY KEY is that each PRIMARY KEY value uniquely identifies a single row within the parent table. In other words, every row in the column (or set of columns) that is the table's PRIMARY KEY must have a unique, non-NULL value. The values within the rows of a FOREIGN KEY column, on the other hand, need not be, and, in fact, are ideally not unique to any row within the child table.

Suppose, for example you want to setup a parent/child relationship between a CUSTOMERS table and an ORDERS table. In this relationship, the rows within the CUSTOMERS table are "parents" and the "child" rows are within the ORDERS table. A customer can (and hopefully does) place more than one order. However, any particular order can only belong to one customer.

To setup the parent/child relationship between CUSTOMERS and ORDERS, you would use the built-in stored procedure sp_pkeys to identify the PRIMARY KEY columns that uniquely identifies each customer (that is, each row) within the CUSTOMERS table. Then you can define within the child table (ORDERS) a FOREIGN KEY constraint that references the PRIMARY KEY within the parent table (CUSTOMERS).

The syntax of an sp_pkeys stored procedure call is

sp_pkeys [@table_name=]''
 [,[@table_owner=]'

' [,[@table_qualifier=]''

where:

• @table_name is the name of the table for which you want the stored procedure to report information about the PRIMARY KEY.
• @table_owner is the username of the table owner. If you omit the @TABLE_OWNER from the procedure call, the DBMS will search for the table given by

among the tables you own, and then among the tables owned by the database owner (DBO).

• @table_qualifier the name of the database in which the table resides. If omitted, the DBMS assumes the table exists within the current database.

Thus, to display information about the PRIMARY KEY defined on the CUSTOMERS table within the NORTHWIND database, you might write the following EXEC statement:

 EXEC sp_pkeys @table_name='customers',
 @table_qualifier='Northwind'

When called, sp_pkeys will return a results set with the following information about the PRIMARY KEY:

• TABLE_QUALIFIER-The name of the database in which the table resides.
• TABLE_OWNER-The username of the table's owner.
• TABLE_NAME-The table's name.
• COLUMN_NAME-The name of a column within the PRIMARY KEY.
• KEY_SEQ-The ordinal position within the PRIMARY KEY of the column named within the COLUMN_NAME column.
• PK_NAME-The name of the primary key.

After calling the sp_pkeys stored procedure, look in the COLUMN_NAME column within the results table to determine the column your FOREIGN KEY (within the ORDERS table, in this example) must reference within the parent (CUSTOMERS) table. Then, apply a FOREIGN KEY constraint to the appropriate columns within the child (ORDERS) table. You learned how to apply FOREIGN KEY constraints while creating a new table within Tip 62, "Using the CREATE TABLE Statement to Assign Foreign Key Constraints." You also learned how to change the structure of an existing table within Tip 60, "Using the ALTER TABLE Statement to Change Primary and Foreign Keys."

If the PRIMARY KEY has multiple columns, the results set returned by the sp_pkeys stored procedure will have multiple rows-one for each column within the PRIMARY KEY. When working with multi-column PRIMARY KEYs, pay particular attention to the value in both the COLUMN_NAME and KEY_SEQ columns. Make sure you list each column within the FOREIGN KEY in the same ordinal position as its corresponding column within the PRIMARY KEY. In other words, the number of columns within the FOREIGN KEY must match the number of columns in the PRIMARY KEY, and corresponding columns must be in the same ordinal position within each key. The number in the KEY_SEQ column gives you the ordinal position within the PRIMARY KEY of the column named in the COLUMN_NAME column.

Using sp_fkeys to Display Information on the Foreign Keys That Reference a Table s PRIMARY KEY

When creating a relational database, be sure to exploit the ability of the DBMS to maintain referential integrity. Although you can set up multi-table SELECT statements that join parent and child rows without PRIMARY KEY and FOREIGN KEY constraints, resist the temptation to do so. While it takes some prior planning to set up PRIMARY KEY and FOREIGN KEY constraints properly, doing so frees you from having to worry about users being able to insert duplicate rows into the parent table or being able to create orphan rows within the child table.

Duplicate rows in a parent table and/or orphans within a child table are undesirable because they lead to errors in reporting and can cause real-life problems. Suppose, for example, that you have a CUSTOMERS parent table and an ORDERS child table. If you have duplicate rows within the CUSTOMERS (parent) table, a child row within the ORDERS table could have two (or more) parents within the CUSTOMERS table-which means a customer will likely be billed twice for the one order that shipped. Conversely, orphan rows within the child (ORDERS) table means there are orders with no customer information. Therefore, if the "Ship To" address is stored within the ORDERS table and the "Bill To" information is stored in the CUSTOMERS table, orders will ship without customers being billed.

After you set up PRIMARY KEY and FOREIGN KEY constraints that let the DBMS manage parent/child relationships between related tables (that is, to maintain referential integrity within the database), you will find it convenient to produce a list of FOREIGN KEY constraints that reference each parent table. This list will come in handy when you want to drop a parent table or change its structure. In addition, when executing a DELETE statement to remove parent rows or an UPDATE statement that changes values within PRIMARY KEY columns, you may have to drop or update columns in rows within the child table as well. The list of FOREIGN KEY references to the parent table's PRIMARY KEY will tell you which child tables require attention when you make changes within the parent table.

The built-in stored procedure sp_fkeys lets you get a list of FOREIGN_KEY references on any table within a database managed by an MS-SQL Server. To call sp_fkeys, use the following syntax

 sp_fkeys
 [@pktable_name=]''
 [,[@pktable_owner=]''
 [,[@fktable_name=]''
 [,[@fktable_owner=]''
 [,[@fktable_qualifier=]''

where:

• @pktable_name is the name of the parent table (with the PRIMARY KEY) for which you want the stored procedure to list the FOREIGN KEY references.
• @pktable_owner is the username of the parent table's owner. If you omit the @PKTABLE_OWNER parameter from the procedure call, the DBMS will search for the table name by @PKTABLE_NAME among the tables you own and then among the tables owned by the database owner (DBO).
• @fktable_name is the name of the child table (with the FOREIGN KEY). If you provide both the parent table (@PKTABLE_NAME) and child table (@FKTABLE_NAME) names, the stored procedure will list only the FOREIGN KEY reference on the parent (named in @PKTABLE_NAME parameter) within the one child table (named in the @FKTABLE_NAME parameter). Conversely, if you provide only the name of the child table (in the @FKTABLE_NAME parameter) and omit the parent table's name, the stored procedure will list all the parent tables referenced by foreign keys within the child table.
• @fktable_owner is the username of the child table's owner. If you omit the @FKTABLE_OWNER parameter, the DBMS will search for the child table named by @FKTABLE_NAME among the tables you own and then among the tables owned by the database owner (DBO).
• @fktable_qualifier is the name of the database in which the child table (with the FOREIGN KEY constraint) resides. If omitted, the DBMS assumes the child (FOREIGN KEY) table exists within the current database.

Thus, if you want a list of all FOREIGN KEY constraints that reference the PRIMARY KEY within the CUSTOMERS table in the NORTHWIND database, you would execute the following statement batch:

 USE Northwind
 EXEC sp_fkeys @pktable_name='Customers'

The stored procedure sp_fkeys, in turn, will return a results set with at least one row per FOREIGN KEY reference and the following columns within each row:

• PKTABLE_QUALIFIER-The name of the database in which the (parent) table with the PRIMARY KEY constraint reside.
• PKTABLE_OWNER-The username of the parent table's owner.
• PKTABLE_NAME-The name of the (parent) table with the PRIMARY KEY constraint.
• PKCOLUMN_NAME-The name of the column in the PRIMARY KEY. If the PRIMARY KEY has multiple columns, sp_fkeys will return multiple rows-one for each column within the PRIMARY KEY constraint.
• FKTABLE_QUALIFIER-The name of the database in which the (child) table with the FOREIGN KEY constraint reside.
• FKTABLE_OWNER-The username of the child table's owner.
• FKTABLE_NAME-The name of the (child) table with the FORIEGN KEY constraint.
• FKCOLUMN_NAME-The name of the column within the FOREIGN KEY that corresponds to the PRIMARY KEY column named in PKCOLUMN_name (within the current row of the results set).
• KEY_SEQ-The ordinal position within the PRIMARY KEY and FOREIGN KEY of the columns described by the current row within the results table.
• UPDATE_RULE-0, 1, or 2 indicate the action the DBMS takes on the value within the FOREIGN KEY column when the value in the corresponding column within the PRIMARY KEY is updated. 0 = Cascade; 1 = No Action, and 2 = Set Null.
• DELETE_RULE-0, 1, or 2 indicate the action the DBMS takes on the value within the FOREIGN KEY column when the value in the child's corresponding row in the parent table is deleted. 0 = Cascade; 1 = No Action, and 2 = Set Null.
• FK_NAME-The user- or system-supplied name of the FOREIGN KEY constraint.
• PK_NAME-The user- or system-supplied name of the PRIMARY KEY constraint.

To display the above listed information for all parent tables referenced by FOREIGN KEYS within a child table, you would call sp_fkey and supply to it only the information about the child table (which has the FOREIGN KEY constraints). For example, to list all tables referenced by FOREIGN KEY constraints within the ORDERS table in the NORTHWIND database, you would execute the following statement batch:

USE Northwind
EXEC sp_fkeys @fktable_name='Orders'

Using sp_procoption to Control Which Stored Procedures the MS SQL Server Will Run at Startup

In Tips 530-532 you learned what a stored procedure does, how to use the CREATE PROCEDURE statement to create a stored procedure, and how to call a stored procedure using the Transact-SQL EXEC command. In short, a stored procedure is a batch of SQL and/or Transact-SQL statements the DBMS executes whenever a user calls the stored procedure by name. Stored procedures are a powerful feature, because they let you write SQL Programs. That is, stored procedures let you combine SQL data management statements with procedural Transact-SQL programming statements so you can create statement batches that perform complex queries, database updates, or execute any sequence of SQL (and Transact-SQL statements) you like. Stored procedures make it possible for a database user to execute complex queries or a large batch of database management statements by submitting a single EXEC statement that calls the stored procedure, which then performs the work required.

In addition to letting users and external applications execute stored procedures, you can have the MS-SQL Server execute specific stored procedures automatically each time you start the DBMS. For example, if you have set of maintenance tasks such as creating and or clearing global temporary tables or cursors, creating executive summary tables, or some other cleanup/preparatory tasks that you want the DBMS to perform each time you start the MS-SQL Server, put the statements that perform the work into one or several stored procedures. Then, use the built-in stored procedure sp_procoption to mark the stored procedures you want to be called at startup.

To create stored procedures the DBMS will execute at startup, you must login to the MASTER database as the database owner (DBO). As DBO, and within the MASTER database, use the CREATE PROCEDURE statement to create a stored procedure you want the DBMS to execute at startup. Next, call the sp_procoption stored procedure with the the following syntax to mark the stored procedure you created for execution at startup

 sp_procoption [@procname=]''
 ,[@optionname=]'startup'
 [@optionvalue=]'{true|false}'

where:

• @procname is the name of the stored procedure you want to MS-SQL Server to run at startup. Or, if you set the @OPTIONVALUE to FALSE, @PROCNAME is the name of the stored procedure you no longer want the MS-SQL Server to run at startup.
• @optionname is always "startup."
• @optionvalue is either TRUE or FALSE. Set @OPTIONVALUE to TRUE if you want the DBMS to execute the procedure named in @PROCNAME at startup. Set @OPTIONVALUE to FALSE if you no longer want the DBMS to execute the procedure at startup.

Suppose for example, that you create the stored procedure su_sp_CreateExecSummary as the DBO within the MASTER database. To have the DBMS call su_sp_CreateExecSummary each time you restart the MS-SQL Server, execute the following statement batch:

USE master
EXEC sp_procoption @procname = 'su_sp_CreateExecSummary',
 @optionname = 'startup',
 @optionvalue = 'true'

Note 

Because MS-SQL Server will not let you execute a USE statement within a stored procedure, you must refer to database objects using their fully qualified names as: ..

within stored procedures you have the DBMS execute at startup.

To make it easy to track stored procedures executed at startup, give each of them a descriptive name starting with "su_sp_"-for "startup stored procedure."

When you no longer want the MS-SQL Server to execute a stored procedure at startup, call the sp_procoption stored procedure as before, only this time, set @OPTIONVALUE to FALSE as shown here:

USE master
EXEC sp_procoption @procname = 'su_sp_CreateExecSummary',
 @optionname = 'startup',
 @optionvalue = 'false'

In this example, the DBMS will turn the execute at startup option off for the stored procedure su_sp_CreateExecSummary.

Using sp_helpdb to Display the Size and Physical Location of Database Files

The MS-SQL Server can manage several databases simultaneously. The DBMS places all objects that make up a database within a single disk file and stores the database transaction log within a second file. Although you can change the names of the data and transaction log files to something other than the MS-SQL Server defaults, there is no advantage to doing so. On an MS-SQL Server 2000 installation, you will normally find the database data and transaction log files within the SQLServer2000MSSQLdata folder on the fileserver's hard drive. The name of the database file on disk is typically the same as the name of the database followed by _Data.MDF. For example, whether there are ten or 1,000 tables and other objects within the SQLTips database, you will find the entire database (that is, all its objects and security information) within a single file named SQLTips_Data.MDF in the MS-SQL Server's data folder (SQLServer2000MSSQLdata). Similarly, the database transaction log file typically starts with the database name followed by _Log,LDF. Thus, the name of the file in which the DBMS keeps the SQLTips transaction log is usually SQLTips_Log.LDF.

You learned how to use the MS-SQL Server Enterprise Manager to create a new database (and therefore new database and transaction log files) in Tip 42, "Using the MS-SQL Server Enterprise Manager to Create a Database and Transaction Log." Then, in Tip 497, "Using the MS-SQL Server Enterprise Manager to Add Files and Filegroups to an Existing Database," you learned how to add additional database and transaction log files to those the DBMS created when you created the database.

To check on the current size, status, and location of the physical files in which the DBMS maintains your database objects, data, and transactions log, call the built-in stored procedure sp_helpdb using the following syntax

 sp_helpdb [[@dbname=]'']

where:

• @dbname is the name of the database on whose files you want the stored procedure to provide information.

For example, to display file information for the SQLTips database, you would use the following EXEC statement to display the information shown in Figure 570.1:

Figure 570.1: Information about the physical database files returned by the built-in stored procedure sp_helpdb

 EXEC sp_helpdb @dbname='SQLTips'

To display only the name, current size (in megabytes), owner, date created, and status information (that is, only the first results set shown below the stored procedure call in Figure 570.1), use the following EXEC statement to call the sp_helpdb stored procedure:

 EXEC sp_helpdb

When you call sp_helpdb without providing the database name, as shown in this example, the stored procedure provides the information from the first results set in Figure 570.1 for all databases under management by the current MS-SQL Server.

Using sp_spaceused to Display the Amount of Used and Unused Space Allocated to the Database or Individual Database Objects

In Tip 542, "Understanding DBCC Maintenance Statements," you learned how to use DBCC SHRINKDATABASE and DBCC SHRINKFILE to reduce the size of database data (.mdf) and transaction log (.ldf) files. Both of these DBCC options involve removing unused space from the database data and transaction log files, and then returning the unused space to the operating system's control.

Database data (.mdf) files sometimes end up with too much empty/unused space, because the DBMS expands the size of its files as you add data to tables within the database. Unless you specify the autoshrink option, the DBMS does not reduce the size of its files on disk even when you delete large amounts of data from database tables or drop large indexes. Rather than release space previously filled with data to the operating system, the DBMS keeps the now unused space within its data files and simply marks the space available for data users might add to the database in the future.

You can set the autoshrink option for a database; however this is not recommended as it may increase overhead unnecessarily. For example, the DBMS may be forced to shrink and then re-grow the database file when unused disk space within the database files is not needed for some other purpose.

The built-in stored procedure sp_spaceused will tell you how much used and unused space there is within the database data file. In fact, you can use call the stored procedure with the following syntax to get space usage information on the entire database or for individual tables within the database

 sp_spaceused [[@objname=l''
 [,[@updateusage=]'updateusage']

where:

• @objname is the name of the table for which you want the stored procedure to display space usage data. If you omit the @OBJNAME parameter, the stored procedure reports space usage information for the current database (versus for an individual table within the database).
• @updateusage specifies whether the DBMS must scan data and index pages on disk for the actual disk usage data and make corrections to the usage figures within the SYSINDEXES table. Typically, you only want to specify the @UPDATEUSAGE option if you have recently dropped a large index from the database, because the SYSINDEXES table may not have the most up-to-date space usage information as a result.

For example, to determine the total size (in megabytes) of the SQLTips database data (.mdf) file as well as the amount of unallocated space retained within the file, submit the following EXEC statement to the DBMS:

 EXEC sp_spaceused

MS-SQL Server, in turn, will display a results set with the following columns:

• DATABASE_NAME—The name of the current database.
• DATABASE_SIZE—The size (in megabytes) of the current database data (.mdf) file(s).
• UNALLOCATED_SPACE—The amount of space (in megabytes) not yet allocated to database objects.
• RESERVED—Space allocated to the 8Kb pages assigned to tables and indexes within the data base that is not yet filled with data.
• DATA—Total amount of space used to hold data in all tables.
• INDEX_SIZE—Total amount of space used to hold data by all indexes within the database.
• UNUSED—Amount of space within the database file that is not yet assigned to any database objects.

If you call the sp_spaceused stored procedure and provide the name of a table within the current database as follows, sp_spaceused reports usage stats for only that one table:

 USE pubs
 EXEC sp_spaceused @objname='authors'

In this example, the DBMS will report the following space usage information for the AUTHORS table within the PUBS database:

• NAME—The name of the table for which space usage information was requested.
• ROWS—The number of rows of data within in the table.
• RESERVED—Space allocated to the 8Kb pages assigned to the table and its indexes that is not yet filled with data.
• DATA—Total amount of space used to hold data within the table.
• INDEX_SIZE—Total amount of space used by index files on the table.
• UNUSED—Amount of space allocated to the table but not yet used as part of an 8Kb data page.

Using sp_helptrigger to Display Information About Triggers on Tables

As you learned in Tips 448–453, triggers are a special type of stored procedure that the DBMS executes in response to an INSERT, UPDATE, or DELETE operation against a specific table. For example, after you execute a CREATE TRIGGER statement to create an INSERT TRIGGER on a table, the DBMS will "fire" (that is, execute) the statements within the trigger before, after, or instead of all INSERT statements that target the table. You specify whether the DBMS is to fire the trigger before, after, or instead of an INSERT, UPDATE, or DELETE statement as one of the options when you create a trigger.

Other than the fact that the DBMS executes a trigger automatically (in response to an INSERT, UPDATE, or DELETE statement—depending on the type of trigger), a trigger looks and works exactly like a stored procedure. Unfortunately, you will not see triggers you've created on tables listed among the normal (non-trigger) stored procedures. You can get a list of non-trigger stored procedures defined within a database by clicking on the Stored Procedures icon for the database within the MS-SQL Server Enterprise Manager.

Moreover, while the built-in stored procedure sp_help displays information on FOREIGN KEY constraints (which may have an ON DELETE or an ON UPDATE rule), sp_help does not tell you anything about the triggers defined on a table. As a result, you must maintain good, up-to-date documentation on every trigger defined on tables within each database on the MS-SQL Server. If you do not, the server will appear to take random actions in response to some, but not all, INSERT, UPDATE, and DELETE statements.

You can use the built-in stored procedure sp_helptrigger, called with the following syntax, to list the triggers defined on a database table

 sp_helptrigger [@tabname=]''
 [,[@triggertype=']'{DELETE|INSERT|UPDATE}']

where:

• @tabname is the name of the table within the current database for which you want the stored procedure to provide information on the triggers defined.
• @triggertype specifies the type of trigger for which the stored procedure is to provide information. If you omit @TRIGGERTYPE, sp_helptrigger will return information about all triggers defined on the table.

For each trigger (or for each trigger of the trigger type you specify for @TRIGGERTYPE), sp_helptrigger returns a results set that includes the following columns:

• TRIGGER_NAME—The name of the trigger.
• TRIGGER_OWNER—The username of the trigger's owner.
• ISUPDATE—1, if the trigger is an UPDATE trigger; 0, if not.
• ISDELETE—1, if the trigger is a DELETE trigger; 0, if not.
• ISINSERT—1, if the trigger is an INSERT trigger; 0, if not.
• ISAFTER—1, if the DBMS is to execute the trigger after (versus before or instead of) executing the triggering UPDATE, DELETE, or INSERT statement on the table on which the trigger is defined. 0, if the DBMS is to execute the trigger before or instead of executing the triggering UPDATE, DELETE, or INSERT statement.
• ISINSTEADOF—1, if the DBMS is to execute the trigger instead of (versus before or after) executing the triggering UPDATE, DELETE, or INSERT statement on the table on which the trigger is defined. 0, if the DBMS is to execute the trigger before or after executing the triggering UPDATE, DELETE, or INSERT statement.

Thus, to display information about all the triggers defined on the EMPLOYEES table within the SQLTips database, you would execute the following statement batch:

 USE SQLTips
 EXEC sp_helptrigger 'employees'

Or, if SQLTips is already the current database, you would simply call the sp_helptrigger stored procedure without executing the USE statement. For example, to display only the UPDATE triggers on the EMPLOYEES table (while already in the SQLTips database) you would use the EXEC statement:

EXEC sp_helptrigger 'employees', 'update'

Note 

To save yourself a lot of grief, keep up-to-date documentation that lists and describes every trigger defined on tables within the databases managed by your MS-SQL Server. If you do not, DBMS will appear to do strange things. Remember that each database may have hundreds of tables and dozens of users submitting INSERT, UPDATE, and DELETE statements at any one time. Therefore, trying to pinpoint the specific INSERT, UPDATE, or DELETE statements that are causing seemingly errant DBMS behavior, will be a non-trivial task. As you learned within this tip, the sp_helptrigger stored procedure requires that you know at least the name of the table on which the trigger is defined.

If you lose your documentation, you can get a list of triggers defined on tables within the current database by executing the following query on the SYSOBJECTS table:

 SELECT * FROM sysobjects WHERE type = 'TR'

Look at the NAME column within the rows of the results set for the names of the triggers defined on tables within the database. Then, execute the following statement (substituting a trigger name from the results set for ) to review the trigger's definition—which includes the name of the table on which the trigger is defined:

 EXEC sp_helptext 

Using sp_who and the KILL Command to Control Processing Running on the MS SQL Server

Sometimes you need all users to logout from a database so you can perform scheduled maintenance tasks or unscheduled repair work. For example, to run the DBCC statements you learned about in Tip 545 "Understanding DBCC Validation Statements," you must first change the database to single-user mode if you want to run a DBCC validation task with a repair option. To change from multi-user to single-user mode, you need everyone to logout from the database. Similarly, if you want to transfer database files from one drive to another, all users must logout. Then, you can detach, move to a new disk, and then re-attach the database disk files. (You learned how to detach and re-attach database files in Tip 561 "Using sp_detach_db and sp_attach_db to Remove and Add a Database to an MS-SQL Server.")

After making a system-wide announcement for the staff to please logout, you must check a list of processes running on the MS-SQL Server to find out when everyone has indeed logged out. To get a list of users and applications logged into the MS-SQL Server, call the built-in stored procedure sp_who, using the following syntax

 EXEC sp_who [[@loginame=]'']

where:

• @loginame is the username of the person associated with the process or login for which you want the stored procedure to supply the SPID and other process status information.

Thus, to display a list of current processes running and users logged onto the MS-SQL Server, submit the following EXEC statement to display the process list as shown in Figure 573.1:

Figure 573.1: Output from a sp_who built-in stored procedure call

 EXEC sp_who

To determine which process to terminate, check the DBNAME column for the name of the database from which you want all users to logout. Make a note of each user's system process ID shown within the SPID column. If the user does not logout in the prescribed amount of time, you can supply the SPID to the Transact-SQL KILL command to terminate the user's session.

To terminate a user's connection, execute the Transact-SQL KILL command using the following syntax

 EXEC KILL {spid} [WITH STATUSONLY]

where:

• @spid is the system process ID of the session you want to terminate.
• WITH STATUSONLY specifies that the DBMS provide a status report only to show how long it will take to finish rolling back (that is, undoing) work already performed within an uncommitted transaction.

For example, to terminate user FRANK'S connection on the MS-SQL Server (shown previously in Figure 573.1 as SPID 53), you would execute the following KILL command:

 KILL 53

When you use KILL to terminate a connection, the DBMS must roll back any uncommitted work performed by the connection you are terminating. While the DBMS is in the process of undoing uncommitted work performed, the process will remain on process lists displayed by the sp_who. However, the status of the connection will show as "KILLED/ROLLBACK."

To get an idea of how much longer it will take to finish a roll back in progress, execute the KILL command with the WITH STATUSONLY option as shown here:

 KILL 53 WITH STATUSONLY

MS-SQL Server, in turn, will respond with a message similar to the following:

 Spid 53: Transaction rollback in progress. Estimated
 rollback completion: 75% Estimated time left: 30 seconds.

In addition to terminating processes so you can get everyone out of a database, you might also use KILL to terminate a process executing an errant query or whose locks are blocking other processes, as discussed within the next tip.

Using sp_lock to Display Information on Locks Held in a Database

In Tips 357–360, you learned about transaction isolation levels. In summary, each of the three transaction isolation levels (SERIALIZABLE, REPEATABLE READ, and READ COMMITTED) is meant to prevent certain errors in updates and reporting that occur when two or more users update and query the same table concurrently. The highest level of isolation, SERIALIZABLE, prevents all update and reporting errors by giving a user an exclusive lock on all pages with data the user reads, updates, or inserts within a table. Until the user closes a SERIALZABLE transaction, he or she maintains an exclusive lock on data read or changed within one or more tables. Other users that want to execute an INSERT or UPDATE statement on data in locked pages must wait until the user with the exclusive lock either ends the transaction (with a COMMIT or ROLLBACK), or lowers the transaction's isolation level to allow shared access.

If a user has been waiting an inordinate amount of time for an INSERT or UPDATE statement to finishing running, you can use the built-in stored procedure sp_lock to determine which other user (or process) is holding an exclusive lock on the table (or a page within the table). After you use sp_lock to determine the locking session's system process ID (SPID), you can use the KILL command to terminate the session that has the data locked, if necessary.

The syntax you use to call the sp_lock stored procedure is

 sp_lock ([@spidl=]''],[,[@spid2=]'']

where:

• @spid1 is the SPID of the process whose lock information you want to display.
• @spid2 is the SPID of a second process whose lock information you want to display.

Thus, you can call sp_lock specifying either zero, one, or two parameters. If you supply zero parameters (that is, if you call sp_lock without supplying either @SPID1 or @SPID2), the stored procedure will display the lock status for all sessions within the current database. Alternatively, if you supply @SPID1, sp_lock will report lock information for one session, and if you supply both @SPID1 and @SPID2, the stored procedure will report location for two sessions.

To resolve a lock issue, first use the built-in stored procedure sp_who to determine the locked session's SPID. Suppose, for example, that Frank reports he has been waiting for hours (at least it seems that way to Frank) for his INSERT statement to run. To determine Frank's SPID, execute the following statement:

 EXEC sp_who 'frank'

When sp_who returns its results set, look in the SPID column and make a note of Frank's SPID—let's say it's 54 for the current example.

Next, execute the following call of the sp_lock stored procedure to display the SPID and lock status for all connections with open locks granted by the DBMS:

 EXEC sp_lock

When the stored procedure sp_lock returns a results set similar to that shown in Figure 574.1, look for Frank's SPID (54, in this example) in the results set's SPID column. Then look across to the right of all rows with Frank's SPID to see find the rows with "WAIT" in the STATUS column.

Figure 574.1: Output from a sp_lock built-in stored procedure call

After you find a row with a "WAIT" in the STATUS column, look back to the left within the row and note the object ID within the DBID column. Move up or down within the DBID column until you find another process using with the same ID within the column.

In this example, SPID 51 is using DBID 7, which is the same DBID for which Frank's connection is "waiting" (as shown previously in Figure 574). Now that you have the two SPID's with the apparent conflict, call sp_lock again, supplying the two SPIDs—just to verify that the one really has locks on the resource the other wants:

 EXEC sp_lock 51, 53

After you see that the one user (51, in this example) was indeed granted locks (indicated by the word GRANT within the row's STATUS column) on the table (or other resource) for which the second is waiting, you can either contact the user holding the lock, or simply terminate the SPID. To determine the name of the user holding the lock, call the sp_who stored procedure and pass to it the SPID of the session whose username you want as in:

 EXEC sp_who 51

When the stored procedure returns the results set, look in the LOGINAME column for the username of the user who's session you may want to terminate. Then, give the user a courtesy call. It is always possible that the user holding the lock on a resource is in the middle of a long update or query process, or that he or she needs exclusive access to the table for some legitimate reason.

If necessary, you can terminate the locking process (to let Frank's session assert a lock on the table and execute its INSERT statement) by executing a KILL command such as the following:

 KILL 51

In this example, the locking session's SPID was 51; your KILL command will likely have a different SPID altogether.

Using sp_password to Change Account Passwords

To access the data within a database, users must first login. While a user may use his or her name or perhaps a job title for a username, you should require that each user select a password that others cannot easily guess. As such, first names, last names, children's names, words from favorite sayings, and anything easily guessable by someone who knows the user, should not be a used as a password.

In Tip 137, "Using the MS-SQL Server Enterprise Manager to Add Logins and Users," you learned how to give users access to an MS-SQL Server. When granting database access to a username, you must select either Windows Authentication or SQL Server Authentication.

If you select Windows authentication, the MS-SQL Server will not ask you to assign the new username a password. Instead, the DBMS depends on the Windows (NT, 2000, XP) operating system to authenticate the user's login. After logging onto the Windows network, the user can login to the MS-SQL Server simply by telling the DBMS to use Windows authentication for his or her username. The MS-SQL Server retrieves the username under which the user logged onto the Windows network and checks it against its list of valid usernames. If it finds the username on the list of usernames for which Windows authentication is allowed, the MS-SQL Server logs the username onto the DBMS.

If you select SQL server authentication, the MS-SQL Server prompts you to enter a password for the new username you are creating. When you select SQL server authentication, the DBMS does not depend on the Windows (NT, 2000, XP) operating system to authenticate the user's login. Instead, whenever a user attempts to login, the DBMS challenges to the user to enter the matching password for the username under which he or she is logging in. After the user enters his or her username and password, the DBMS checks the username/password pair against those within its access list. If it finds the username/password pair on its access list, the MS-SQL Server logs the username onto the DBMS.

When you use either the MS-SQL Server Enterprise Manager or a built-in stored procedure to create an account that uses SQL server authentication, the only way to change the account's password is by calling the built-in stored procedure sp_password. Note that when you create a Windows authenticated account, the Windows (NT, 2000, XP) operating system, (not the MS-SQL Server,) maintains each account's password. Therefore, to change the password on a Windows network account, the user must use a Windows network facility or contact the network's system manager. In short, MS-SQL Server can only change passwords on accounts that the MS-SQL Server authenticates—and then only through the stored procedure sp_password.

To change an MS-SQL Server authenticated account's password, call the sp_password stored procedure using the following syntax

 sp_password [[@old=]',]'
 {[@new]''}
 [,[@loginame=]''

where:

• @old is the current password on the account. Any user can change his or her password, but must supply the account's current password in order to do so. Members of the SYSADMIN or SECURITYADMIN role can change passwords without supplying the current password through @OLD.
• @new is the new password you want to assign to the account.
• @loginame is the username on the account whose password sp_password is to change.

Users change passwords for various reasons—another user may have learned the current password, company policy may require periodic password changes, or perhaps the user simply chose too long of a password initially, and is tired of typing it each time he or she logs onto the DBMS. By calling sp_password and supplying the account's current password (through the @OLD parameter) and a new password (through the @NEW parameter), any user can change the password on his or her own account.

If a user has forgotten his or her password, the user must see a member of the SYSADMIN or SECURITYADMIN role. A member of either group can omit the @OLD parameter and simply supply the new password (through the @NEW parameter). Suppose, for example, user Sue wanted to change her password. To do so, Sue must login to the DBMS and then call the sp_password stored procedure as follows:

 EXEC sp_password @old='OldPass', @new='newpass'

Note that a non-privileged user must supply both the current (old) password and the desired (new) password for the account under which the user is logged onto the DBMS. The system administrator (or any member of the SYSADMIN or SECURTYADMIN role) can change the password on another user's account by supplying the new password (through @NEW parameter) and the username (through the @LOGINAME parameter) on the account whose password is to be changed. Suppose for example, that Konrad, a member of the SYSADMIN role, wants to change Oscar's password to MEYER. To change the password on Oscar's account, Konrad would call the sp_password stored procedure as follows:

 EXEC sp_password @new='MEYER', @loginame='OSCAR'

Using Built In Stored Procedures to Manage MS SQL Server User Accounts

In Tips 137-138, you learned how to use the MS-SQL Server Enterprise Manager to add and remove user accounts. For those times when you are working on a system that does not have the MS-SQL Server Enterprise Manager installed, or when you are accessing the MS-SQL Server either remotely or locally through an application program (such as the SQL Query Analyzer), you can use built-in stored procedures to manage DBMS user accounts.

When creating (and removing) accounts, bear in mind that there are two types of accounts-MS-SQL Server accounts, which exist exclusively within the DBMS, and Windows accounts, which are Windows (NT, 2000, or XP) network accounts granted access to the DBMS. To create new MS-SQL Server accounts, use sp_addlogin. To create Windows security accounts, use sp_grantlogin. The first stored procedure (sp_addlogin) creates a new account while the second (sp_grantlogin) lets an existing Windows (NT, 2000, or XP) network account login to the database.

If you want the MS-SQL Server to authenticate a user's login to the DBMS by requiring the user to enter valid username/password pair, create an MS-SQL Server account. To create a new MS-SQL Server account, call the built-in stored procedure sp_addlogin using the following syntax

sp_addlogin [@loginame=]''
 [,[@password=]'']
 [,[@defdb=]'']
 [,[@deflanguage='']
 [,[@SID=]'']
 [,[@encryptopt=]
 {'NULL|skip_encryption|skip_encryption_old'}]

where:

• @loginame is the username you want to assign to the new account you are adding. The user must supply the correct username/password pair to login to an account.
• @passwd is the password you want to assign to the new account you are adding. Though optional, for security reasons you should require that all accounts have a password. When @PASSWORD is not NULL, the user must supply the correct username/password pair to login to an account.
• @defdb is the name of the accounts default database, that is, the database to which the user is connected after login.
• @deflanguage is the default language assigned when the user logs in. You typically omit this parameter and let the DBMS assign its default language to each user session.
• @sid is the security identification (SID) number you want the DBMS to assign to an account. When specified @SID must be exactly 16 bytes in length and must not already exist. You typically let the DBMS create a unique SID when it creates an account. As such, only supply the SID if you are transferring or duplicating user accounts from one server on onto another and want the logins to be identical.
• @encryptopt specifies whether the accounts password is to be encrypted when stored within the system tables.
  • NULL-The password is encrypted.
  • SKIP_ENCRYPTION-The password is already encrypted, so MS-SQL Server does not need to re-encrypt it.
  • SKIP_ENCRYPTION_OLD-The password was encrypted on a previous version of the MS-SQL Server and does not need to be re-encrypted prior to storage.

For example, to create an account with username WALTER and password JONES223, and whose login defaults to using the SQLTips database, execute the following statement:

EXEC sp_addlogin @loginame='walter', @passwd='JONES223',
 @defdb='SQLTips'

After creating a new user account, you must also grant the user access to a database-including the account's default database-or the user will not be able to login to the MS-SQL Server. You will learn how to use the built-in stored procedure sp_grantdbaccess to grant users access to a database following the discussion of sp_grantlogin.

If you want the DBMS to let the Windows (NT, 2000, or XP) server take care of login authentication, use the built-in stored procedure sp_grantlogin to create a Windows security account on the MS-SQL Server. When using a Windows security account to access the DBMS, the user logs into the Windows network (versus the MS-SQL Server) by providing the network server a valid username/password pair. The Windows (NT, 2000, or XP) operating system validates the username/password pair the user entered and logs the user's account into the network. When the user then requests access to the DBMS, the MS-SQL Server reads the username on the user's network login and makes sure the username is a valid Windows security account on the DBMS. If the MS-SQL Server determines the username belongs to a valid Windows security account, the user is logged into the DBMS-without being challenged for the account's password.

To create a Windows security account, use the following syntax to call the sp_grantlogin stored procedure:

 EXEC sp_grantlogin [@loginame=]''

Note that you do include a password for Windows user (or group) accounts that you want to let access the DBMS. The MS-SQL Server has no need to know the password for these accounts, because the DBMS lets the Windows (NT, 2000, or XP) server handle account authentication. Thus, to let the Windows network account CLARISSA within the NVBIZNET2 domain login to the network using Windows security, you would execute the following command:

 EXEC sp_grantlogin 'NVBizNet2clarissa'

As mentioned previously within this tip, after you use sp_addlogin to create a new MS-SQL Server account or use sp_grantlogin to create a Windows security account, you must grant the user access to at least one database before the user can login to the DBMS. To grant an account access to a database, use the built-in stored procedure sp_grantdbaccess, which has the following syntax

 sp_grantdbaccess [@loginame=]{}
 [,[@name_in_db=]''

where:

• @loginame is the username on the account to which you want to grant access to the current database. If the account is a Windows (NT, 2000, XP) account (which you used sp_grantlogin to give access to the MS-SQL Server), pass the to the stored procedure as @LOGIN. If the account is an MS-SQL Server account (which you created with sp_addlogin), only pass the to sp_grantdbaccess.
• @name_in_db is the name you want to use when referring to the account within the database. For MS-SQL Server accounts, omit @NAME_IN_DB altogether. For Windows (NT, 2000, XP) accounts supply the portion of the you passed to the stored procedure through the @LOGINAME parameter.

Note that sp_grantdbaccess grants the account you specify in @LOGINAME access to the current database. As such, be sure to execute a USE statement that takes you to the database to which you want to grant access before you call the sp_grantdbaccess stored procedure. For example, to grant username WALTER access to the SQLTips database, execute the following statement batch:

 USE SQLTips
 EXEC sp_grantdbaccess 'walter'

Because WALTER is an MS-SQL Server account (that is, an account for which the DBMS authenticates the login username and password) you omit the @NAME_IN_DB parameter. Similarly, to make it possible for the Windows security account with username CLARISSA to login to the MyTempDb database, execute the following statement batch:

 USE MyTempDb
 EXEC sp_grantdbaccess 'NVBizNet2Clarissa', 'Clarissa'

As shown in this example, for Windows security accounts, you supply both the account's username (CLARISSA) and the domain in which the account exists (NVBIZNET2) on the Windows network. In addition, you should set the @NAME_IN_DB parameter to the user-name portion of the you passed to the stored procedure through the @LOGINAME parameter. If you do not supply the database username, the stored procedure will use the value of @LOGIN for the database username, and you will not be able to use the SQL GRANT statement to grant privileges on database objects to the Windows security account user.

If you later decide you no longer want an account to have access to a particular database, call the built-in stored procedure sp_revokedbaccess. To remove database access from both MS-SQL Server and Windows security accounts, use sp_revokedbaccess which has the following syntax:

 EXEC sp_revokedbaccess [@name_in_db=] 

As was the case with sp_grantdbaccess, the sp_revokedbaccess stored procedure affects the rights the account has to access the current database. Therefore, be sure to execute a USE statement to make the database to which you no longer want the account to have access before you call the sp_revokedbaccess stored procedure. For example, to remove Clarissa's access to the MyTempDb database, execute the following statement batch:

 USE MyTempDb
 EXEC sp_revokedbaccess 'Clarissa'

If you no longer want an MS-SQL Server user account to have access to the database, first use sp_revokedbaccess to remove the accounts access from each database to which you previously granted the account access (by calling the stored procedure sp_grantdbaccess). Then, call the built-in stored procedure sp_droplogin (which uses the following syntax), to remove the user's account from the MS-SQL Server altogether:

 EXEC sp_droplogin [@loginame=]

Note that you can only use sp_droplogin to remove MS-SQL Server accounts (and not Windows security accounts).

If you want to remove a Windows security account's ability to access the DBMS, call the built-in stored procedure sp_revokelogin using the following syntax:

 EXEC sp_revokelogin [@loginame=]

Working with SQL Database Data Across the Internet