sp_control_dbmasterkey_password (Transact-SQL)sp_control_dbmasterkey_password (Transact-SQL)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

添加或删除包含打开数据库主密钥所需的密码的凭据。Adds or drops a credential containing the password needed to open a database master key.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions


sp_control_dbmasterkey_password @db_name = 'database_name,  
     @password = 'master_key_password' , @action = { 'add' | 'drop' }  


@db_name= N 'database_name'@db_name=N'database_name'
指定与此凭据关联的数据库的名称。Specifies the name of the database associated with this credential. 不能为系统数据库。Cannot be a system database. database_namenvarchardatabase_name is nvarchar.

@password= N 'password'@password=N'password'
指定主密钥的密码。Specifies the password of the master key. 密码nvarcharpassword is nvarchar.

@action= N'add '@action=N'add'
指定已指定数据库的凭据将添加到凭据存储区中。Specifies that a credential for the specified database will be added to the credential store. 凭据将包含数据库主密钥的密码。The credential will contain the password of the database master key. 传递给@action的值为**nvarchar**。The value passed to @action is nvarchar.

@action= N'drop '@action=N'drop'
指定将从凭据存储区中删除已指定数据库的凭据。Specifies that a credential for the specified database will be dropped from the credential store. 传递给@action的值为**nvarchar**。The value passed to @action is nvarchar.


SQL ServerSQL Server 需要数据库主密钥对密钥进行解密或加密时,SQL ServerSQL Server 会尝试使用实例的服务主密钥对数据库主密钥进行解密。When SQL ServerSQL Server needs a database master key to decrypt or encrypt a key, SQL ServerSQL Server tries to decrypt the database master key with the service master key of the instance. 如果解密失败, SQL ServerSQL Server则将在凭据存储区中搜索与需要其主密钥的数据库具有相同系列 GUID 的主密钥凭据。If the decryption fails, SQL ServerSQL Server searches the credential store for master key credentials that have the same family GUID as the database for which it needs the master key. SQL ServerSQL Server 尝试使用每个匹配的凭据对数据库主密钥进行解密,直到成功解密或者没有更多的凭据为止。SQL ServerSQL Server then tries to decrypt the database master key with each matching credential until the decryption succeeds or there are no more credentials.


对于 sa 和其他特权级别高的服务器主体无法访问的数据库,不要为其创建主密钥凭据。Do not create a master key credential for a database that must be inaccessible to sa and other highly-privileged server principals. 可以对数据库进行配置,以便服务主密钥无法对其密钥层次结构进行解密。You can configure a database so that its key hierarchy cannot be decrypted by the service master key. 该选项可作为数据库(包含 sa 或其他特权级别高的服务器主体不可访问的加密信息)的深度防御。This option is supported as a defense-in-depth for databases that contain encrypted information that should not be accessible to sa or other highly privileged server principals. 为此类数据库创建主密钥凭据将会删除这种深度防御功能,从而使 sa 和其他特权级别高的服务器主体能够对数据库进行解密。Creating a master key credential for such a database removes this defense-in-depth, enabling sa and other highly privileged server principals to decrypt the database.

使用 sp_control_dbmasterkey_password 创建的凭据在sys. master_key_passwords目录视图中可见。Credentials that are created by using sp_control_dbmasterkey_password are visible in the sys.master_key_passwords catalog view. 为数据库主密钥创建的凭据的名称具有如下格式:##DBMKEY_<database_family_guid>_<random_password_guid>##The names of credentials that are created for database master keys have the following format: ##DBMKEY_<database_family_guid>_<random_password_guid>##. 该密码存储为凭据机密。The password is stored as the credential secret. 对于每个添加到凭据存储区中的密码,都在 sys.credentials 中占一行。For each password added to the credential store there is a row in sys.credentials.

不能使用 sp_control_dbmasterkey_password 为下列系统数据库创建凭据:master、model、msdb 或 tempdb。You cannot use sp_control_dbmasterkey_password to create a credential for the following system databases: master, model, msdb, or tempdb.

sp_control_dbmasterkey_password 不会验证密码是否可以打开已指定数据库的主密钥。sp_control_dbmasterkey_password does not verify that the password can open the master key of the specified database.

如果为已指定数据库指定一个已存储在凭据中的密码,则 sp_control_dbmasterkey_password 将会失败。If you specify a password that is already stored in a credential for the specified database, sp_control_dbmasterkey_password will fail.


来自不同服务器实例的两个数据库可以共享相同的系列 GUID。Two databases from different server instances can share the same family GUID. 如果出现这种情况,则这两个数据库将会共享凭据存储区中的相同主密钥记录。If this occurs, the databases will share the same master key records in the credential store.

传递给 sp_control_dbmasterkey_password 的参数不会出现在跟踪中。Parameters passed to sp_control_dbmasterkey_password do not appear in traces.


在您使用通过 sp_control_dbmasterkey_password 添加的凭据打开数据库主密钥时,服务主密钥重新对数据库主密钥加密。When you are using the credential that was added by using sp_control_dbmasterkey_password to open the database master key, the database master key is re-encrypted by the service master key. 如果数据库为只读模式,则加密操作将会失败,数据库主密钥将会保留为未加密状态。If the database is in read-only mode, the re-encryption operation will fail, and the database master key will remain unencrypted. 随后访问数据库主密钥时,必须使用 OPEN MASTER KEY 语句和密码。For subsequent access to the database master key, you must use the OPEN MASTER KEY statement and a password. 为避免使用密码,请在将数据库迁移到只读模式前创建凭据。To avoid using a password, create the credential before you move the database to read-only mode.

潜在向后兼容性问题: 当前,存储过程不检查主密钥是否存在。Potential Backward Compatibility Issue: Currently, the stored procedure does not check whether a master key exists. 这是为了向后兼容,但会显示警告。This is permitted for backward compatibility, but displays a warning. 不推荐使用此行为。This behavior is deprecated. 在将来的版本中,主密钥必须存在,并且在存储过程sp_control_dbmasterkey_password中使用的密码必须与用来对数据库主密钥进行加密的密码之一相同。In a future release the master key must exist and the password used in the stored procedure sp_control_dbmasterkey_password must be the same password as one of the passwords used to encrypt the database master key.


要求对数据库具有 CONTROL 权限。Requires CONTROL permission on the database.


A.A. 为 AdventureWorks2012 主密钥创建凭据Creating a credential for the AdventureWorks2012 master key

以下示例为 AdventureWorks2012 数据库主密钥创建凭据,并将主密钥密码作为机密内容存储在凭据中。The following example creates a credential for the AdventureWorks2012 database master key, and saves the master key password as the secret in the credential. 由于传递给的所有参数都sp_control_dbmasterkey_password必须为nvarchar类型的数据,因此,将用强制转换运算符N来转换文本字符串。Because all parameters that are passed to sp_control_dbmasterkey_password must be of data type nvarchar, the text strings are converted with the casting operator N.

EXEC sp_control_dbmasterkey_password @db_name = N'AdventureWorks2012',   
    @password = N'sdfjlkj#mM00sdfdsf98093258jJlfdk4', @action = N'add';  

B.B. 删除数据库主密钥的凭据Dropping a credential for a database master key

以下示例删除在示例 A 中创建的凭据。请注意,需要所有的参数,包括密码。The following example removes the credential created in example A. Note that all parameters are required, including the password.

EXEC sp_control_dbmasterkey_password @db_name = N'AdventureWorks2012',   
    @password = N'sdfjlkj#mM00sdfdsf98093258jJlfdk4', @action = N'drop';  

另请参阅See Also

设置加密的镜像数据库 Set Up an Encrypted Mirror Database
安全存储过程 (Transact-sql) Security Stored Procedures (Transact-SQL)
系统存储过程 (Transact-SQL) System Stored Procedures (Transact-SQL)
sys.credentials (Transact-SQL) sys.credentials (Transact-SQL)
凭据(数据库引擎)Credentials (Database Engine)