CREATE MASTER KEY (Transact-SQL)
Creates a database master key.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
Arguments
- PASSWORD ='password'
Is the password used to encrypt the master key in the database.
Remarks
The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When it is created, the master key is encrypted by using the Triple DES algorithm and a user-supplied password. To enable the automatic decryption of the master key, a copy of the key is encrypted by using the service master key and stored in both the database and in master. Typically, the copy stored in master is silently updated whenever the master key is changed. This default can be changed by using the DROP ENCRYPTION BY SERVICE MASTER KEY option of ALTER MASTER KEY. A master key that is not encrypted by the service master key must be opened by using the OPEN MASTER KEY statement and a password.
The is_master_key_encrypted_by_server column of the sys.databases catalog view in master indicates whether the database master key is encrypted by the service master key.
Information about the database master key is visible in the sys.symmetric_keys catalog view.
Important
You should back up the master key by using BACKUP MASTER KEY and store the backup in a secure, off-site location.
Permissions
Requires CONTROL permission on the database.
Examples
The following example creates a database master key for the AdventureWorks database. The key is encrypted using the password 23987hxJ#KL95234nl0zBe.
USE AdventureWorks;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';
GO
See Also
Reference
sys.symmetric_keys (Transact-SQL)
sys.databases (Transact-SQL)
OPEN MASTER KEY (Transact-SQL)
ALTER MASTER KEY (Transact-SQL)
DROP MASTER KEY (Transact-SQL)
CLOSE MASTER KEY (Transact-SQL)