System Stored Procedures

System stored procedures are built-in Transact-SQL scripts that perform various administrative and maintenance tasks in SQL Server. They are prefixed with sp_.

Introduction to System Stored Procedures

These procedures are part of the SQL Server installation and are essential for managing and querying the database system. They provide a standardized way to interact with SQL Server's metadata and configuration.

Key categories include:

  • Configuration Procedures: Used to view and modify server and database settings.
  • Information Procedures: Used to retrieve metadata about database objects, users, and permissions.
  • Maintenance Procedures: Used for tasks like backing up, restoring, and checking database integrity.
  • Security Procedures: Used for managing logins, users, and permissions.

Common System Stored Procedures

Security Procedures

Managing user accounts and permissions is critical. These procedures help you:

  • sp_addlogin / sp_adduser: Add new logins and users.
  • sp_droplogin / sp_dropuser: Remove logins and users.
  • sp_grantdbaccess: Grant a login access to a database.
  • sp_revokedbaccess: Remove a login's access to a database.
  • sp_addrole / sp_droprole: Manage database roles.
  • sp_addsrvrolemember: Add a login to a server role.

Example of adding a user:

EXEC sp_adduser 'new_user', 'new_password', 'db_owner';

Information Procedures

Understanding your database structure is vital. Use these to:

  • sp_help: Display information about a database object.
  • sp_tables: List tables in the current database.
  • sp_columns: List columns for a specified table.
  • sp_databases: List all databases on the SQL Server instance.
  • sp_helptext: Display the source text of a stored procedure, view, or trigger.

Example of getting table information:

EXEC sp_help 'MyTableName';

Maintenance Procedures

Keeping your databases healthy and performing well:

  • sp_backup_database: Back up a database. (Note: BACKUP DATABASE T-SQL command is more common and recommended).
  • sp_reindex: Rebuild indexes for a table.
  • sp_updatestats: Update statistics for all tables in the current database.

Example of updating statistics:

EXEC sp_updatestats;

Configuration Procedures

Adjusting server settings:

  • sp_configure: View or change server configuration options.

Example of viewing a configuration option:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)';

Important Considerations

  • Security: Always be cautious when executing system stored procedures, especially those that modify server settings or permissions. Ensure you understand the potential impact.
  • Permissions: Many system stored procedures require elevated permissions, such as membership in the sysadmin fixed server role.
  • Deprecation: Some older system stored procedures might be deprecated in newer versions of SQL Server. Always refer to the latest documentation for the specific version you are using.
  • Alternatives: For many tasks, the corresponding Transact-SQL statements (e.g., CREATE USER, ALTER DATABASE) are preferred over system stored procedures.

Further Reading