Skip to content

sp-certify-removable-transact-sql.md

Latest commit

 

History

History
100 lines (70 loc) · 4.3 KB

sp-certify-removable-transact-sql.md

File metadata and controls

100 lines (70 loc) · 4.3 KB
title sp_certify_removable (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 08/09/2016
ms.prod sql
ms.prod_service database-engine
ms.component system-stored-procedures
ms.reviewer
ms.suite sql
ms.technology system-objects
ms.tgt_pltfrm
ms.topic language-reference
f1_keywords
sp_certify_removable_TSQL
sp_certify_removable
dev_langs
TSQL
helpviewer_keywords
sp_certify_removable
ms.assetid ca12767f-0ae5-4652-b523-c23473f100a1
caps.latest.revision 26
author stevestein
ms.author sstein
manager craigg

sp_certify_removable (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]

Verifies that a database is correctly configured for distribution on removable media and reports any problems to the user.

IMPORTANT!! [!INCLUDEssNoteDepFutureAvoid instead.

Topic link icon Transact-SQL Syntax Conventions

Syntax

  
sp_certify_removable [ @dbname= ] 'dbname'  
     [ , [ @autofix = ] 'auto' ]

Arguments

[ @dbname=] 'dbname'
Specifies the database to be verified. dbname is sysname.

[ @autofix=] 'auto'
Gives ownership of the database and all database objects to the system administrator, and drops any user-created database users and nondefault permissions. auto is nvarchar(4), with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Remarks

If the database is correctly configured, sp_certify_removable performs the following:

  • Sets the database offline so the files can be copied.

  • Updates statistics on all tables and reports any ownership or user problems

  • Marks the data filegroups as read-only so these files can be copied to read-only media.

The system administrator must be the owner of the database and all database objects. The system administrator is a known user that exists on all servers that are running [!INCLUDEmsCoName] [!INCLUDEssNoVersion] and can be expected to exist when the database is later distributed and installed.

If you run sp_certify_removable without the auto value and it returns information about any of the following conditions:

  • The system administrator is not the database owner.

  • Any user-created users exist.

  • The system administrator does not own all objects in the database.

  • Nondefault permissions have been granted.

You can correct those conditions in the following ways:

  • Use [!INCLUDEssNoVersion] tools and procedures, and then run sp_certify_removable again.

  • Just run sp_certify_removable with the auto value.

Note that this stored procedure only checks for users and user permissions. You can add groups to the database and to grant permissions to those groups. For more information, see GRANT (Transact-SQL).

Permissions

Execute permissions are restricted to members of the sysadmin fixed server role.

Examples

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

EXEC sp_certify_removable inventory, AUTO;

See Also

Database Detach and Attach (SQL Server)
sp_create_removable (Transact-SQL)
ALTER DATABASE (Transact-SQL)
sp_dbremove (Transact-SQL)
System Stored Procedures (Transact-SQL)