SQL Server system stored procedures are provided for your convenience. They make it easy to retrieve information from the system tables, administer databases, and perform other tasks that involve updating system tables.
System stored procedures are created in the master database during installation and are owned by the system administrator. The names of all system stored procedures begin with sp_. All stored procedures for which documentation is provided are for regular system use. Additional stored procedures exist in the master database for internal use only. Although these procedures can be viewed and used as examples for writing your own procedures, they are not guaranteed to be included in future releases.
You can run system stored procedures from any database. If a system stored procedure is executed from a database other than master, any non-qualified references to system tables are mapped to the database from which the procedure is being run. For example, if the database owner of the pubs database runs sp_adduser from pubs, the new user is added to pubs..sysusers.
When the parameter for a system stored procedure is a reserved word or an object name and the object name is qualified by a database name or owner name, the entire name must be enclosed in single quotation marks.
Because system stored procedures are located in the master database, their permissions are also set there. Some system stored procedures can be run only by database owners. These procedures make sure that the user executing the procedure is the owner of the database from which the procedures are being executed.
Other system stored procedures can be executed by any user who has been granted EXECUTE permission on them. The EXECUTE permission must be granted in the master database.
The following sections detail the system stored procedures intended for regular use by the system administrator, the database owner, or all users.