SQL Server数据库主密钥
问题描述:
我运行以下脚本为我创建的其中一个数据库创建主密钥,但我无法看到它们在节点中输入密钥(请参见快照);有谁知道为什么?我期待听到您的回复,谢谢。SQL Server数据库主密钥
USE AdventureWorks
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'
GO
答
你不能看到SSMS GUI这个信息,但是你可以看到,如果你运行下面的数据库是否有一个主键:
SELECT d.is_master_key_encrypted_by_server
FROM sys.databases AS d
WHERE d.name = 'AdventureWorks';
答
这里有一对夫妇测试DMK存在的方法。
请注意,sys.databases中的[is_master_key_encrypted_by_server]列可以显示0,但DMK存在并且已从SMK加密中删除。
我希望这会有所帮助。
======================
-- Test for existence of a DMK. If it does not exist, then create it.
-- Method 1:
IF (SELECT COUNT(*) FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%') = 0
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '[email protected]'
END
-- Method 2:
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%')
BEGIN
SELECT 'DMK does not exist'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '[email protected]'
END
ELSE
BEGIN
SELECT 'DMK exists'
END
-- Demo showing that is_master_key_encrypted_by_server in sys.databases does not show whether the DMK exists or not.
DROP MASTER KEY
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '[email protected]'
GO
SELECT is_master_key_encrypted_by_server, name
FROM sys.databases
WHERE name = 'GalaxianTest1'
--> is_master_key_encrypted_by_server name
--> 1 GalaxianTest1
USE GalaxianTest1
GO
-- This command causes the DMK to not be encrypted by the SMK.
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
-- This command now shows 0, although the DMK still exists.
SELECT is_master_key_encrypted_by_server, name
FROM sys.databases
WHERE name = 'GalaxianTest1'
--> is_master_key_encrypted_by_server name
--> 0 GalaxianTest1
-- Try creating a new DMK. This will error because the DMK still exists.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '[email protected]'
GO
--> Error: There is already a master key in the database. Please drop it before performing this statement.
DROP MASTER KEY
GO
--> Command(s) completed successfully.
SELECT is_master_key_encrypted_by_server, name
FROM sys.databases
WHERE name = 'GalaxianTest1'
--> is_master_key_encrypted_by_server name
--> 0 GalaxianTest1
-- Note: this is the same message as above when the DMK existed, but had been dropped from encryption by service master key.
这里有用的文档:http://msdn.microsoft.com/en-us/库/ ms179331.aspx –