sp_certify_removable System Stored Procedure

For SQL Server 6.5 information, see sp_certify_removable in What's New for SQL Server 6.5.

Verifies that a database is configured properly for distribution on removable media.

Syntax

sp_certify_removable dbname[, AUTO]

where

dbname

Specifies the database to be verified.

AUTO

As necessary, gives ownership of the database and all database objects to the system administrator, and drops any user-created database users and any non-default permissions.

Remarks

If the database is configured properly, the sp_certify_removable system stored procedure sets the database offline and returns information that will be required when the database is later installed using sp_dbinstall. It truncates the transaction log, moves it to the system device, and drops the log device. It returns the physical and logical names and the size, in megabytes, of the system device and the data device(s). Make a note of this information and provide it¾along with the distribution disk¾to users who will be installing the distributed database.

The system administrator must be owner of the database and all database objects because the system administrator is a known user that exists on all SQL Servers and can be counted on to exist when the database is later distributed and installed.

If you run sp_certify_removable without the AUTO option and it returns information indicating that the system administrator is not the database owner, that user-created users exist, that the system administrator does not own all objects in the database, or that non-default permissions have been granted, you have two ways to correct those conditions:

• You can use SQL Server tools and procedures, and then run sp_certify_removable again.
• You can simply run sp_certify_removable with the AUTO option.

Note that this stored procedure only checks for users and user permissions. It is permissible to add groups to the database and to grant permissions to those groups. For information, see the GRANT statement.

Example

This example certifies that the inventory database is ready to be removed.

sp_certify_removable inventory, AUTO

Permission

Only the system administrator can use this stored procedure.

See also