ALTER MASTER KEY (Transact-SQL)

Switch View

ALTER MASTER KEY (Transact-SQL)

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (October 2009)

ALTER MASTER KEY (Transact-SQL)

Updated: 30 April 2009

Changes the properties of a database master key.

Topic link icon Transact-SQL Syntax Conventions

Syntax

ALTER MASTER KEY <alter_option>

<alter_option> ::=
    <regenerate_option> | <encryption_option>

<regenerate_option> ::=
    [ FORCE ] REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

<encryption_option> ::=
    ADD ENCRYPTION BY { SERVICE MASTER KEY | PASSWORD = 'password' }
    | 
    DROP ENCRYPTION BY { SERVICE MASTER KEY | PASSWORD = 'password' }

Arguments

PASSWORD = 'password': Specifies a password with which to encrypt or decrypt the database master key. password must meet the Windows password policy requirements of the computer that is running the instance of SQL Server.

Remarks

The REGENERATE option re-creates the database master key and all the keys it protects. The keys are first decrypted with the old master key, and then encrypted with the new master key. This resource-intensive operation should be scheduled during a period of low demand, unless the master key has been compromised.

When the FORCE option is used, key regeneration will continue even if the master key is unavailable or the server cannot decrypt all the encrypted private keys. If the master key cannot be opened, use the RESTORE MASTER KEY statement to restore the master key from a backup. Use the FORCE option only if the master key is irretrievable or if decryption fails. Information that is encrypted only by an irretrievable key will be lost.

The DROP ENCRYPTION BY SERVICE MASTER KEY option removes the encryption of the database master key by the service master key.

ADD ENCRYPTION BY SERVICE MASTER KEY causes a copy of the master key to be encrypted using the service master key and stored in both the current database and in master.

Permissions

Requires CONTROL permission on the database. If the database master key has been encrypted with a password, knowledge of that password is also required.

Examples

The following example creates a new database master key for AdventureWorks and reencrypts the keys below it in the encryption hierarchy.

USE AdventureWorks;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003';
GO

Change History

Updated content
Corrected the <encryption_option> section in the syntax to use braces {} instead of brackets [].

Reference

Other Resources

Help and Information