Appendix C: System Stored Procedure Reference

Overview

SQL Server 2000 and 2005 support the following system and extended stored procedures. Procedures introduced with SQL Server 2005 are so indicated.

Active Directory

Procedure Name	Return Type	Description
sp_ActiveDirectory_Obj	Int	Adds, updates, or removes the registration record of a SQL Server database in the Active Directory.
sp_ActiveDirectory_SCP	Int	Adds, updates, or removes the registration record of a SQL Server instance in the Active Directory.

Catalog

Open table as spreadsheet

Procedure Name	Return Type	Description
sp_column_privileges	Table	Returns column privileges for a table in the current session.
sp_column_privileges_ex	Table	Returns column privileges for a table on a linked or remote server.
sp_columns	Table	Returns column information for a table or view.
sp_columns_ex	Table	Returns column information for a table or view on a linked or remote server.
sp_databases	Table	Returns information about databases on the local server.
sp_fkeys	Table	Returns foreign key information for a table.
sp_pkeys	Table	Returns primary key information for a table.
sp_server_info	Table	Returns server attributes for the server on a specified connection.
sp_special_columns	Table	Returns columns used to uniquely identify a row (that is, primary key and unique constraints) and columns with programmatically updated values and defaults.
sp_sproc_columns	Table	Returns column information for a stored procedure or user-defined function.
sp_statistics	Table	Returns information about indexes and statistics for a table.
sp_stored_procedures	Table	Returns information about all stored procedures matching a name or wildcard pattern.
sp_table_privileges	Table	Returns information about permissions for a table or tables matching a wildcard pattern in the current session.
sp_table_privileges_ex	Table	Returns information about permissions for a table or tables matching a wildcard pattern on a linked or remote server.
sp_tables	Table	Returns information about all tables matching a name or wildcard pattern.

Cursor Management

Procedure Name	Return Type	Description
sp_cursor_list	Cursor	Returns attributes and information about currently open cursor(s).
sp_describe_cursor	Cursor	Returns attributes and information about a specific cursor.
sp_describe_cursor_columns	Cursor	Returns information about columns used to populate a cursor.
sp_describe_cursor_tables	Cursor	Returns information about tables used to populate a cursor.

Database Engine

Open table as spreadsheet

Procedure Name	Return Type	Description
sp_add_data_file_recover_suspect_db	Int	Adds a database file to a file group after a disk full error. Similar to ALTER DATABASE ADD FILE.
sp_add_log_file_recover_suspect_db	Int	Adds a transaction log file to a file group after a disk full error. Similar to ALTER DATABASE ADD LOG FILE.
sp_addextendedproc	Int	Adds and registers an extended stored procedure to the server meta data.
sp_addextendedproperty	Int	Adds an extended property to the server metadata.
sp_addmessage	Int	Adds a custom error message to the server messages meta data.
sp_addtype	Int	Adds a user-defined data type to a database.
sp_addumpdevice	Int	Adds a backup device (file, tape drive, or other device) to the server.
sp_altermessage	Int	Modifies an existing error message (number, severity, category, or message text).
sp_attach_db	Int	Attaches a database file to a SQL Server instance and makes it available as an active database.
sp_attach_single_file_db	Int	Similar to sp_attach_db but only for single-file databases. Builds new transactionlog file.
sp_autostats	Int	Returns or modifies the UPDATE STATISTICS setting for a table's index or statistics in the current database.
sp_bindefault	Int	Associates a defined default with a table's column as a shared default.
sp_bindrule	Int	Associates a defined rule with a table's column as a default check constraint.
sp_bindsession	Int	Allows multiple connections to participate in a single transaction by associating them to an established session.
sp_certify_removable	Int	Verifies (certifies) that a database may be actively used on removable media.
sp_configure	Int	Returns or modifies server configuration settings.
sp_create_removable	Int	Creates a set of files and a new database to be used on removable media.
sp_createstats	Int	Generates statistics for all tables and candidate columns in the current database.
sp_cycle_errorlog	(none)	Closes the current error log and initiates a new error log with a default name and settings (as if for a server restart).
sp_datatype_info	Table	Returns detailed information about all current data types or information for a specific data type (system and user-defined data types).
sp_dbcmptlevel	Int	Sets the SQL Server version database compatibility level. Setting the level to an older version number disables certain product features to emulate the capabilities of an older SQL Server version.
sp_dboption	Int & Table	Sets user database options similar to those set using ALTER DATABASE.
sp_dbremove	Int	Removes a database and associated files.
sp_delete_backuphistory	Int	Removes backup history information for a database.
sp_depends	Int & Table	Lists the dependent objects for a database object.
sp_detach_db	Int	Detaches a database's file(s) from the server.
sp_dropdevice	Int	Removes a database or backup device record from the server.
sp_dropextendedproc	Int	Drops an extended stored procedure from the server.
sp_dropextendedproperty	Int	Drops an extended property from the server.
sp_dropmessage	Int	Removes an error message record from the server.
sp_droptype	Int	Removes a user-defined data type from the server.
sp_executesql	Int	Executes a parameterized Transact-SQL statement.
sp_getapplock	Int	Places a lock on an application or system resource outside of SQL Server for the duration of a transaction or session.
sp_getbindtoken	(none)	Uses a VarChar type output parameter to return a unique ID for a transaction.
sp_help	Int & Table	Returns descriptive help information specific to a database object.
sp_helpconstraint	Int & Table	Returns help information for a specified constraint.
sp_helpdb	Int & Table	Returns help information for a specified database.
sp_helpdevice	Int & Table	Returns help information for a specified device.
sp_helpextendedproc	Int & Table	Returns help information for a specified extended stored procedure.
sp_helpfile	Int & Table	Returns help information for a specified database file.
sp_helpfilegroup	Int & Table	Returns help information for a specified database file group.
sp_helpindex	Int & Table	Returns help information for a specified index.
sp_helplanguage	Int & Table	Returns help information for a specified server language.
sp_helpserver	Int & Table	Returns help information for a specified server (local or remote).
sp_helpsort	Int & Table	Returns a description of the server's collation and sort order.
sp_helpstats	Int & Table	Returns help information regarding the statistics associated with the indexes for a specified table.
sp_helptext	Int & Table	Returns the definition of a rule, default, stored procedure, user-defined function, trigger, or view.
sp_helptrigger	Int & Table	Returns information about the triggers associated with a specified table.
sp_indexoption	Int	Allows default level-locking options (that is, row, page, table) to be overridden for a specified index.
sp_invalidate_textptr	Int	Invalidates a specified in-row text pointer, or all in-row text pointers in a transaction.
sp_lock	Int & Table	Returns information about all active locks.
sp_monitor	Int & Table	Returns the results from several system functions to show the status of server and system resources.
sp_procoption	(none - uses error codes)	Enables one of several procedure options to be set.
sp_recompile	Int	Recompiles a stored procedure or trigger.
sp_refreshview	Int	Updates the metadata for a specified view.
sp_releaseapplock	Int	Removes a lock set on an application or external system resource that may have been set using sp_getapplock.
sp_rename	Int	Renames a database object.
sp_renamedb	Int	Renames a database.
sp_resetstatus	Int	Resets the suspect status of a database back to normal status.
sp_serveroption	Int	Sets specified server options for a remote and linked server.
sp_setnetname	Int	Sets the network name for a linked or remote server.
sp_settriggerorder	Int	Sets a specified trigger for a table to execute first or last.
sp_spaceused	Int & Table	Returns information about the disk space used by rows, table, and a database.
sp_tableoption	Int	Sets one of several table options.
sp_unbindefault	Int	Removes a specified default from a column or user-defined data type.
sp_unbindrule	Int	Removes a specified rule from a column or user-defined data type.
sp_updateextendedproperty	Int	Updates the value of a specified extended property.
sp_updatestats	Int	Updates all index statistics in the database.
sp_validname	Int	Checks a specified character string for validity as an object name. If invalid, raises an option error.
sp_who	Int	Returns information about current connections and user sessions on a server.

Database Maintenance

Procedure Name	Return Type	Description
sp_add_maintenance_plan	Int	Adds a maintenance plan to the server and returns the plan ID.
sp_add_maintenance_plan_db	Int	Associates a database with a maintenance plan. (A maintenance plan is added using sp_add_maintenance_plan.)
sp_add_maintenance_plan_job	Int	Associates a maintenance plan with an existing job.
sp_delete_maintenance_plan	Int	Deletes a maintenance plan based in the specified plan ID.
sp_delete_maintenance_plan_db	Int	Removes an associated maintenance plan from the specified database.
sp_delete_maintenance_plan_job	Int	Removes an associated maintenance plan from the specified job.
sp_help_maintenance_plan	Int & Table	Returns information about maintenance plans on the server.

Distributed Queries

Open table as spreadsheet

Procedure Name	Return Types	Description
sp_addlinkedserver	Int	Adds a linked server to the current server, allowing persistent access to a remote SQL Server from the current server.
sp_addlinkedsrvlogin	Int	Adds the association of a local login to a linked server login for user connectivity to a linked server.
sp_catalogs	Table	Returns the list of databases on a linked server.
sp_column_privileges_ex	Table	Returns column-level security access privilege information for a specified table on a linked server.
sp_columns_ex	Table	Returns column information for a table or view on a linked server.
sp_droplinkedsrvlogin	Int	Removes the association of a local login to the login on a linked server.
sp_foreignkeys	Table	Returns information about foreign key columns related to a specified primary key for tables on a linked server.
sp_indexes	Table	Returns index information for a remote or linked server table.
sp_linkedservers	Table	Returns information about all linked servers.
sp_primarykeys	Table	Returns information about primary key columns for a specified remote or linked server table.
sp_serveroption	Int	Sets server options for remote servers and linked servers.
sp_table_privileges_ex	Table	Returns information about column and table-level security privileges for a specified remote or linked table.
sp_tables_ex	Table	Returns information about tables on a remote or linked server.

External Systems and Extended Procedures

Procedure Name	Return Types	Description
xp_cmdshell	Int	Executes an operating system shell command, as if entered at the command prompt on the server.
xp_enumgroups	Int & Table	Returns information about Windows domain groups.
xp_findnextmsg	Int	Uses an output parameter to return a MAPI message ID from the SQL Server Inbox.
xp_grantlogin	Int	Calls sp_grantlogin for backward compatibility. Creates a SQL Server login for an associated Windows user or group.
xp_logevent	Int	Logs a message to the SQL Server log file without raising a SQL Server error.
xp_loginconfig	Int & Table	Returns SQL Server security configuration information.
xp_logininfo	Int & Table	Returns detailed information about a SQL Server login and related privileges.
xp_msver	Table	Returns detailed information about the instance of SQL Server and the operating system environment.
xp_revokelogin	Int	Calls sp_revokelogin for backward compatibility. Revokes permissions of a SQL Server login.
xp_sprintf	Int	Uses an output parameter to return a character string. Used to assemble a character string from parameterized values.
xp_sqlmaint	(none)	Calls the SQLMAIN command-line tool to set SQL Server maintenance options.
xp_sscanf	Int	Uses an output parameter to return a character string. Used to dissassemble a character string into corresponding parameterized values. This is the converse of the xp_sprintf procedure.

Full-Text Index/Search

Open table as spreadsheet

Procedure Name	Return Types	Description
sp_fulltext_catalog	Int	Creates or maintains a full-text catalog to be used to store and maintain full-text indexes.
sp_fulltext_column	Int	Indicates whether a specified column should be included in a full-text index.
sp_fulltext_database	Int	Enables or disables full-text indexing for a database.
sp_fulltext_service	Int	Used to manage full-text indexing services on a server.
sp_fulltext_table	Int	Manages and enables actions for full-text indexing on a specific table.
sp_help_fulltext_catalogs	Int & Table	Returns information about the tables and attributes for a full-text catalog.
sp_help_fulltext_catalogs_cursor	Int & Table	Returns information about the tables and attributes for a full-text catalog.
sp_help_fulltext_columns	Int & Table	Returns information about the columns contained in a full-text index for a table.
sp_help_fulltext_columns_cursor	Int & Cursor	Returns information about the columns contained in a full-text index for a table.
sp_help_fulltext_tables	Int & Table	Returns information about the tables contained in a full-text catalog.
sp_help_fulltext_tables_cursor	Int & Cursor	Returns information about the tables contained in a full-text catalog.

Log Shipping

Procedures added in SQL Server 2005.

Open table as spreadsheet

Procedure Name	Return Type
sp_add_log_shipping_alert_job	Int
sp_add_log_shipping_primary_database	Int
sp_add_log_shipping_primary_secondary	Int
sp_add_log_shipping_secondary_database	Int
sp_add_log_shipping_secondary_primary	Int
sp_change_log_shipping_primary_database	Int
sp_change_log_shipping_secondary_database	Int
sp_change_log_shipping_secondary_primary	Int
sp_cleanup_log_shipping_history	Int
sp_delete_log_shipping_alert_job	Int
sp_delete_log_shipping_primary_database	Int
sp_delete_log_shipping_primary_secondary	Int
sp_delete_log_shipping_secondary_database	Int
sp_delete_log_shipping_secondary_primary	Int
sp_help_log_shipping_alert_job	Table
sp_help_log_shipping_monitor_primary	Table
sp_help_log_shipping_monitor_secondary	Table
sp_help_log_shipping_primary_database	Table
sp_help_log_shipping_primary_secondary	Table
sp_help_log_shipping_secondary_database	Table
sp_help_log_shipping_secondary_primary	Table
sp_refresh_log_shipping_monitor	Int
sp_resolve_logins	Int

Mail

The xp_readmail and xp_sendmail procedures are valid in SQL Server 2000. All others procedures are new in SQL Server 2005.

Open table as spreadsheet

Procedure Name	Return Type
sp_send_dbmail	Int
sysmail_add_account_sp	Int
sysmail_add_principalprofile_sp	Int
sysmail_add_profile_sp	Int
sysmail_add_profileaccount_sp	Int
sysmail_configure_sp	Int
sysmail_delete_account_sp	Int
sysmail_delete_principalprofile_sp	Int
sysmail_delete_profile_sp	Int
sysmail_delete_profileaccount_sp	Int
sysmail_help_account_sp	Table
sysmail_help_configure_sp	Table
sysmail_help_principalprofile_sp	Table
sysmail_help_profile_sp	Table
sysmail_help_profileaccount_sp	Table
sysmail_start_sp	Int
sysmail_stop_sp	Int
sysmail_update_account_sp	Int
sysmail_update_principalprofile_sp	Int
sysmail_update_profile_sp	Int
sysmail_update_profileaccount_sp	Int
xp_readmail	Int & Table
Xp_sendmail	Int

Notification Services

Procedures added in SQL Server 2005.

Open table as spreadsheet

Procedure Name	Return Type
NSAdministrationHistory	Table
NSDiagnosticDeliveryChannel	Table
NSDiagnosticEventClass	Table
NSDiagnosticEventProvider	Table
NSDiagnosticFailedNotifications	Table
NSDiagnosticNotificationClass	Table
NSDiagnosticSubscriptionClass	Table
NSEventBatchDetails	Table
NSEventBeginBatch	Table
NSEventFlushBatch	Table
NSEventSubmitBatch	Table
NSEventWrite	Int
NSExecuteRuleFiring	Table
NSNotificationBatchDetails	Table
NSPrepareRuleFiring	Table
NSQuantumDetails	Table
NSQuantumExecutionTime	Table
NSQuantumFailures	Table
NSQuantumList	Table
NSQuantumPerformance	Table
NSQuantumsSkipped	Table
NSScheduledSubscriptionDetails	Table
NSScheduledSubscriptionList	Table
NSSetQuantumClock	Int
NSSetQuantumClockDate	Int
NSSnapshotApplications	Int
NSSnapshotDeliveryChannels	Table
NSSnapshotEvents	Table
NSSnapshotProviders	Table
NSSnapshotSubscriptions	Table
NSVacuum	Table

OLE Automation

Using these OLE automation stored procedures, it's possible to execute certain application code from SQL queries to perform actions and automate applications outside of SQL Server. Using custom-created COM components; practically any programmatic interaction is possible.

Open table as spreadsheet

Procedure Name	Return Type	Description
sp_OACreate	Int	Instantiates an OLE object from a specified class using either the ProgID or CLSID. If stopped, starts the OLE automation execution process on the server.
sp_OADestroy	Int	Destroys a previously instantiated OLE object.
sp_OAGetErrorInfo	Int & Table	Returns the error information associated with an OLE object instance and actions.
sp_OAGetProperty	Int & Table	Uses either an output parameter or a result set to return the value(s) or structured information for a specified object property.
sp_OAMethod	Int & Table	Calls a method of an OLE object. Uses either an output parameter or a result set to return the value(s) or structured information returned by the method call.
sp_OASetProperty	Int	Sets an object property to a specified value.
sp_OAStop	Int	Stops the OLE automation execution process environment on the server. Immediately terminates all OLE automation activity for all sessions.

Profiler

Open table as spreadsheet

Procedure Name	Return Type	Description
sp_trace_create	Int	Creates a new Profiler trace.
sp_trace_generateevent	Int	Creates a new Profiler event.
sp_trace_setevent	Int	Adds an existing event to a trace. These items may be created using the sp_trace_create and sp_trace_generateevent procedures.
sp_trace_setfilter	Int	Adds a filter to an existing trace.
sp_trace_setstatus	Int	Modifies an existing trace.

Replication

Open table as spreadsheet

Procedure Name	Return Type
sp_add_agent_parameter	Int
sp_add_agent_profile	Int
sp_addarticle	Int
sp_adddistpublisher	Int
sp_adddistributiondb	Int
sp_adddistributor	Int
sp_addmergealternatepublisher	Int
sp_addmergearticle	Int
sp_addmergefilter	Int
sp_addmergepublication	Int
sp_addmergepullsubscription	Int
sp_addmergepullsubscription_agent	Int
sp_addmergesubscription	Int
sp_addpublication	Int
sp_addpublication_snapshot	Int
sp_addpublisher70	Int
sp_addpullsubscription	Int
sp_addpullsubscription_agent	Int
sp_addscriptexec	Int
sp_addsubscriber	Int
sp_addsubscriber_schedule	Int
sp_addsubscription	Int
sp_addsynctriggers	Int
sp_addtabletocontents	Int
sp_adjustpublisheridentityrange	Int
sp_article_validation	Int
sp_articlecolumn	Int
sp_articlefilter	Int
sp_articlesynctranprocs	Int
sp_articleview	Int
sp_attachsubscription	Int
sp_browsemergesnapshotfolder	Table
sp_browsereplcmds	Table
sp_browsesnapshotfolder	Table
sp_change_agent_parameter	Int
sp_change_agent_profile	Int
sp_change_subscription_properties	Int
sp_changearticle	Int
sp_changedistpublisher	Int
sp_changedistributiondb	Int
sp_changedistributor_password	Int
sp_changedistributor_property	Int
sp_changemergearticle	Int
sp_changemergefilter	Int
sp_changemergepublication	Int
sp_changemergepullsubscription	Int
sp_changemergesubscription	Int
sp_changepublication	Int
sp_changesubscriber	Int
sp_changesubscriber_schedule	Int
sp_changesubscriptiondtsinfo	Int
sp_changesubscriptiondtsinfo	Int
sp_changesubstatus	Int
sp_check_for_sync_trigger	Int
sp_copymergesnapshot	Int
sp_copysnapshot	Int
sp_copysubscription	Int
sp_deletemergeconflictrow	Int
sp_disableagentoffload	Int
sp_drop_agent_parameter	Int
sp_drop_agent_profile	Int
sp_dropanonymouseagent	Int
sp_droparticle	Int
sp_dropdistpublisher	Int
sp_dropdistributiondb	Int
sp_dropdistributor	Int
sp_dropmergealternatepublisher	Int
sp_dropmergearticle	Int
sp_dropmergefilter	Int
sp_dropmergepublication	Int
sp_dropmergepullsubscription	Int
sp_dropmergesubscription	Int
sp_droppublication	Int
sp_droppullsubscription	Int
sp_dropsubscriber	Int
sp_dropsubscription	Int
sp_dsninfo	Int & Table
sp_dumpparamcmd	Int
sp_enableagentoffload	Int
sp_enumcustomresolvers	Int & Table
sp_enumdsn	Int & Table
sp_enumfullsubscribers	Int & Table
sp_expired_subscription_cleanup	Int
sp_generatefilters	Int
sp_get_distributor	Int
sp_getagentoffloadinfo	Int & Table
sp_getmergedeletetype	Int
sp_getqueuedrows	Int
sp_getsubscriptiondtspackagename	Int
sp_grant_publication_access	Int
sp_help_agent_default	Table
sp_help_agent_parameter	Table
sp_help_agent_profile	Table
sp_help_publication_access	Table
sp_helparticle	Table
sp_helparticlecolumns	Table
sp_helparticledts	Table
sp_helpdistpublisher	Table
sp_helpdistributiondb	Table
sp_helpdistributor	Table
sp_helpmergealternatepublisher	Table
sp_helpmergealternatepublisher	Table
sp_helpmergearticle	Table
sp_helpmergearticlecolumn	Table
sp_helpmergearticleconflicts	Table
sp_helpmergeconflictrows	Table
sp_helpmergedeleteconflictrows	Table
sp_helpmergefilter	Table
sp_helpmergepublication	Table
sp_helpmergepullsubscription	Table
sp_helpmergesubscription	Table
sp_helppublication	Table
sp_helppullsubscription	Table
sp_helpreplfailovermode	Table
sp_helpreplicationdboption	Table
sp_helpreplicationoption	Table
sp_helpsubscriberinfo	Table
sp_helpsubscription	Table
sp_helpsubscription_properties	Table
sp_ivindexhasnullcols	Int
sp_link_publication	Int
sp_marksubscriptionvalidation	Int
sp_mergearticlecolumn	Int
sp_mergecleanupmetadata	Int
sp_mergedummyupdate	Int
sp_mergesubscription_cleanup	Int
sp_publication_validation	Int
sp_refreshsubscriptions	Int
sp_reinitmergepullsubscription	Int
sp_reinitmergesubscription	Int
sp_reinitpullsubscription	Int
sp_reinitsubscription	Int
sp_removedbreplication	Int
sp_repladdcolumn	Int
sp_replcmds	Table
sp_replcounters	Table
sp_repldone	Int
sp_repldropcolumn	Int
sp_replflush	Int
sp_replication_agent_checkup	Int
sp_replicationdboption	Int
sp_replqueuemonitor	Int
sp_replsetoriginator	Int
sp_replshowcmds	Table
sp_repltrans	Table
sp_restoredbreplication	(none)
sp_resyncmergesubscription	Int
sp_revoke_publication_access	Int
sp_script_synctran_commands	Int & Table
sp_setreplfailovermode	Int
sp_showrowreplicainfo	Table
sp_subscription_cleanup	Int
sp_table_validation	Int
sp_update_agent_profile	Int
sp_validatemergepublication	Int
sp_validatemergesubscription	Int
sp_vupgrade_replication	Int

Security

Procedure Name	Return Type	Description
sp_addalias	Int	Matches a server login to a database user. This is an older alternative to using role-based security.
sp_addapprole	Int	Adds an application role to a database to be used for programmatic access from an application component.
sp_addgroup	Int	Adds a user group to a database. This is an older alternative to using role-based security.
sp_addlinkedsrvlogin	Int	Matches a local server login to a linked server login for access to a remote database server.
sp_addlogin	Int	Adds a new server login.
sp_addremotelogin	Int	Adds a login to the local server for use by remote users.
sp_addrole	Int	Adds a new database role.
sp_addrolemember	Int	Adds a SQL Server user, role, Windows user, or group to a SQL Server role.
sp_addserver	Int	Obsolete. Similar to sp_addlinkedserver, adds the metadata representing a registered linked server with persistent access from the local server.
sp_addsrvrolemember	Int	Adds a server login to a server role.
sp_adduser	Int	Obsolete. Similar to sp_grantdbaccess, adds a SQL Server user, role, Windows user, or group to a database.
sp_approlepassword	Int	Modifies the password for an application role.
sp_change_users_login	Int & Table	Modifies the association between a server login and a database user.
sp_changedbowner	Int	Modifies the owner of a database.
sp_changegroup	Int	Obsolete. Similar to sp_addrolemember, modifies the role membership for a user.
sp_changeobjectowner	Int	Modifies the owner of any database object.
sp_dbfixedrolepermission	Int & Table	Returns permission information for all fixed database roles.
sp_defaultdb	Int	Modifies the default database setting for a login.
sp_defaultlanguage	Int	Modifies the default language setting for a login.
sp_denylogin	Int	Denies access to the server for a Windows user or group.
sp_dropalias	Int	Obsolete. Drops an alias associated with a database user. This is an older technique used before SQL Server role-based security. sp_droprolemember provides similar functionality as a recommended practice.
sp_dropapprole	Int	Drops an application role.
sp_dropgroup	Int & Table	Removes a database role. This is an older procedure provided for compatibility.
sp_droplinkedsrvlogin	Int	Removes the association between a local server login and a linked server login.
sp_droplogin	Int	Drops a local server login.
sp_dropremotelogin	Int	Drops a remote login from the local server.
sp_droprolemember	Int	Removes a user, login, Windows user, or group from a database role.
sp_dropserver	Int	Removes the record of a linked or remote server from a local server.
sp_dropsrvrolemember	Int	Removes a server login, Windows user, or group from a server role.
sp_dropuser	Int	Obsolete. Similar to sp_revokedbaccess, removes access to a database for a SQL Server user, Windows user, or group.
sp_grantdbaccess	Int	Adds access to a database for a server login, Windows user, or group.
sp_grantlogin	Int	Adds access for a Windows user of group to the database server using Windows Integrated Security.
sp_helpdbfixedrole	Table	Returns information about fixed database roles.
sp_helpgroup	Table	Obsolete. Returns information about database groups.
sp_helplinkedsrvlogin	Table	Returns information about linked server logins.
sp_helplogins	Table	Returns information about local server logins.
sp_helpntgroup	Table	Returns information about Windows groups.
sp_helpremotelogin	Table	Returns information about remote logins registered with the local server.
sp_helprole	Table	Returns information about fixed database roles.
sp_helprolemember	Table	Returns information about the roles for a database.
sp_helprotect	Table	Returns permissions information related to a specified database object.
sp_helpsrvrole	Table	Returns information about server roles.
sp_helpsrvrolemember	Table	Returns information about the logins, Windows user, and groups that are members of a specified server role.
sp_helpuser	Table	Returns information about database users, Windows users, groups, and database roles in a database.
sp_MShasdbaccess	Int & Table	Returns database information accessible to a user.
sp_password	Int	Adds or modifies the password for a login.
sp_remoteoption	Int & Table	Returns or modifies option settings for a remote login.
sp_revokedbaccess	Int	Removes a database user, Windows user, or group from a database.
sp_revokelogin	Int	Removes a login associated with a Windows user or group.
sp_setapprole	Int	Enables an application role for a database. Used to allow programmatic access from an application component.
sp_srvrolepermission	Int & Table	Returns permission information for a server role.
sp_validatelogins	Int & Table	Returns Windows user and group entries in the database server that no longer exist in the operating system or Windows domain.

SQL Agent

Open table as spreadsheet

Procedure Name	Return Type	Description
sp_add_alert	Int	Creates a new alert.
sp_add_category	Int	Creates a new category that may be associated with jobs, operators, and so on.
sp_add_job	Int	Creates a new job to contain steps.
sp_add_jobschedule	Int	Creates a new schedule for an existing job.
sp_add_jobserver	Int	Changes the server that will run a job.
sp_add_jobstep	Int	Adds a step to an existing job.
sp_add_notification	Int	Creates and adds a notification for an alert.
sp_add_operator	Int	Creates an operator associated with a job and an alert.
sp_add_proxy	Int	(SQL Server 2005) Creates a proxy account for users and roles to execute jobs with elevated permissions, without administrative role membership.
sp_add_schedule	Int	(SQL Server 2005) Creates a schedule for use with a job.
sp_add_targetservergroup	Int	Creates a server group to associate a job with a group of servers.
sp_add_targetsvrgrp_member	Int	Adds a server to a target server group.
sp_apply_job_to_targets	Int	Associates a job with one or more target servers.
sp_attach_schedule	Int	(SQL Server 2005) Associates a schedule with a job.
sp_cycle_agent_errorlog	Int	(SQL Server 2005) Closes the agent error log and initializes a new log file.
sp_cycle_errorlog	Int	Closes the current error log and starts a new log file.
sp_delete_alert	Int	Deletes an alert.
sp_delete_category	Int	Deletes a category.
sp_delete_job	Int	Deletes a job.
sp_delete_jobschedule	Int	Deletes the schedule for a job.
sp_delete_jobserver	Int	Deletes the association between a job and a server.
sp_delete_jobstep	Int	Deletes a specified job step.
sp_delete_jobsteplog	Int	(SQL Server 20005) Deletes a specified job step, all job steps for a specified job, or those that meet other criteria.
sp_delete_notification	Int	Deletes notifications for an operator and an alert.
sp_delete_operator	Int	Deletes a specified operator.
sp_delete_proxy	Int	(SQL Server 20005) Removes a user proxy.
sp_delete_schedule	Int	(SQL Server 2005) Deletes a job schedule.
sp_delete_targetserver	Int	Removes a target server designation for a job.
sp_delete_targetservergroup	Int	Deletes a target server group.
sp_delete_targetsvrgrp_member	Int	Removes a target server from a group.
sp_detach_schedule	Int	(SQL Server 2005) Associates a schedule to a job.
sp_enum_login_for_proxy	Table	(SQL Server 2005) Returns logins associated with a proxy.
sp_enum_proxy_for_subsystem	Table	(SQL Server 2005) Returns proxy users that have access to a specified subsystem.
sp_enum_sqlagent_subsystems	Table	Returns the subsystems (process threads) for SQL Agent.
sp_grant_login_to_proxy	Int	(SQL Server 2005) Grants a login, user, Windows user, or group access to a proxy.
sp_grant_proxy_to_subsystem	Int	(SQL Server 2005) Assigns a DTS/Integration Services subsystem to a proxy user.
sp_help_alert	Table	Returns information about an alert or alerts.
sp_help_category	Table	Returns information about a category or categories.
sp_help_downloadlist	Table	Returns information about queued target server download instructions.
sp_help_job	Table	Returns information about a job or jobs.
sp_help_jobactivity	Table	Returns information about job activities.
sp_help_jobcount	Int	(SQL Server 2005) Returns the count of jobs for an associated schedule.
sp_help_jobhistory	Table	Returns information about jobs for associated servers.
sp_help_jobs_in_schedule	Table	(SQL Server 2005) Returns the information about jobs for an associated schedule.
sp_help_jobschedule	Table	Returns information about automated job scheduling.
sp_help_jobserver	Table	Returns information about a server associated with a job.
sp_help_jobstep	Table	Returns information about the steps for a job.
sp_help_notification	Table	(SQL Server 2005) Returns information about notifications.
sp_help_operator	Table	Returns information about an operator or operators.
sp_help_proxy	Table	(SQL Server 2005) Returns information about a proxy user or proxies.
sp_help_schedule	Table	Returns information about a schedule or schedules.
sp_help_targetserver	Table	Returns information about a job target server or servers.
sp_help_targetservergroup	Table	Returns information about a job target server group or groups.
sp_manage_jobs_by_login	Int	Removes or modifies jobs for a specified login.
sp_msx_defect	Int	Modifies the system registry to remove the server from target multiserver operations.
sp_msx_enlist	Int	Modifies the system registry to add the server to the available multiserver target list.
sp_msx_get_account	Table	Returns credentials information for a target server, used to log into a master server.
sp_msx_set_account	Int	Sets credentials for a target server to log into a master server.
sp_notify_operator	Int	Sends an e-mail message to an operator by using SQLiMail.
sp_post_msx_operation	Int	Inserts job information into the sysdownloadlist table for target servers to execute.
sp_purge_jobhistory	Int	Removes history metadata associated with a job.
sp_remove_job_from_targetss	Int	Removes the association between a job and a target server.
sp_resync_targetserver	Int	Synchronizes all job metadata from remote servers to the target server.
sp_revoke_login_from_proxy		(SQL Server 2005)
sp_revoke_proxy_from_subsystem		(SQL Server 2005) Removes access to a subsystem for a proxy.
sp_start_job	Int	Starts executing a job regardless of its schedule.
sp_stop_job	Int	Stops executing a job.
sp_update_alert	Int	Modifies the settings for an alert.
sp_update_category	Int	Modifies the name of a category.
sp_update_job	Int	Modifies the settings for a job.
sp_update_jobschedule	Int	Modifies the settings for a job's schedule.
sp_update_jobstep	Int	Modifies the settings for a step.
sp_update_notification	Int	(SQL Server 2005) Modifies the settings for a notification.
sp_update_operator	Int	Modifies the information for an operator.
sp_update_proxy		(SQL Server 2005) Modifies the information for a proxy user.
sp_update_schedule	Int	(SQL Server 2005) Modifies an agent schedule.
sp_update_targetservergroup	Int	Modifies the name of a target server group.
sp_xml_preparedocument	Int	Uses an output parameter to return a numeric handle to a cached copy of a well-formed and prepared XML document structure. The initial XML document is passed into this procedure as a VarChar type.
sp_xml_removedocument	Int	Removes data from the server's XML cache.