Transact-SQL User's Guide

System Procedures

System procedures are:

• Shortcuts for retrieving information from the system tables
• Mechanisms for accomplishing database administration and other tasks that involve updating system tables

Most of the time, system tables are updated only through stored procedures. A System Administrator can allow direct updates of system tables by changing a configuration variable and issuing the reconfigure with override command. See the System Administration Guide for details.

The names of system procedures begin with "sp_". They are created by the installmaster script in the sybsystemprocs database during Adaptive Server installation.

You can run system procedures from any database. If a system procedure is executed from a database other than the sybsystemprocs database, any references to system tables are mapped to the database from which the procedure is being run. For example, if the Database Owner of pubs2 runs sp_adduser from pubs2, the new user is added to pubs2..sysusers.

When the parameter for a system procedure is an object name, and the object name is qualified by a database name or owner name, the entire name must be enclosed in single or double quotes.

Since system procedures are located in the sybsystemprocs database, their permissions are also set there. Some of the system procedures can be run only by Database Owners. These procedures ensure that the user executing the procedure is the owner of the database on which they are executed.

Other system procedures can be executed by any user who has been granted execute permission on them, but this permission must be granted in the sybsystemprocs database. This situation has two consequences:

• A user can have permission to execute a system procedure either in all databases or in none of them.
• The owner of a user database cannot directly control permissions on the system procedures within his or her own database.

System Procedures Used for Security Administration

These system procedures are used for:

• Adding, dropping, and reporting on logins on Adaptive Server
• Adding, dropping, and reporting on users, groups, and aliases in a database
• Changing passwords and default databases
• Changing the owner of a database
• Adding, dropping, and reporting on remote servers that can access the current Adaptive Server
• Adding the names of users from remote servers who can access the current Adaptive Server

The procedures in this category are:

sp_addalias	sp_changedbowner	sp_droplogin	sp_helpuser
sp_addgroup	sp_changegroup	sp_dropuser	sp_password
sp_addlogin	sp_dropalias	sp_helpgroup
sp_adduser	sp_dropgroup	sp_helprotect

System Procedures Used for Remote Servers

These system procedures are used for:

• Adding, dropping and reporting on remote servers that can access the current Adaptive Server
• Adding the names of users from remote servers who can access the current Adaptive Server

The procedures in this category are:

sp_addremotelogin	sp_dropserver	sp_remoteoption
sp_addserver	sp_helpremotelogin	sp_serveroption
sp_dropremotelogin	sp_helpserver

System Procedures Used for Data Definition and Database Objects

These system procedures are used for:

• Binding and unbinding rules and defaults
• Adding, dropping, and reporting on primary keys, foreign keys, and common keys
• Adding, dropping, and reporting on user-defined datatypes
• Renaming database objects and user-defined datatypes
• Re-optimizing stored procedures and triggers
• Reporting on database objects, user-defined datatypes, dependencies among database objects, databases, indexes, and space used by tables and indexes

The procedures in this category are:

sp_addtype	sp_foreignkey	sp_indsuspect
sp_bindefault	sp_help	sp_primarykey
sp_bindrule	sp_helpdb	sp_recompile
sp_commonkey	sp_helpindex	sp_rename
sp_depends	sp_helpjoins	sp_spaceused
sp_dropkey	sp_helpkey	sp_unbindefault
sp_droptype	sp_helptext	sp_unbindrule

System Procedures Used for User-Defined Messages

These system procedures are used for:

• Adding user-defined messages to the sysusermessages table in a user database
• Dropping user-defined messages from sysusermessages
• Retrieving messages from either sysusermessages or sysmessages in the master database for use in print and raiserror statements

The procedures in this category are:

sp_addmessage

sp_dropmessage

sp_getmessage

System Procedures Used for System Administration

These system procedures are used for:

• Adding, dropping, and reporting on database and dump devices
• Reporting on locks, the database options that are set, and the users currently running processes
• Changing and reporting on configuration variables
• Monitoring Adaptive Server activity

The procedures in this category are:

sp_addumpdevice	sp_dropdevice	sp_monitor
sp_configure	sp_helpdevice	sp_lock
sp_dboption	sp_helpsort	sp_who
sp_diskdefault	sp_logdevice

See the System Administration Guide for more information about the system procedures that accomplish these administrative tasks. For complete information about the system procedures, see Chapter 3, "System Procedures," in the Adaptive Server Reference Manual.