SQL Server Database Master Key

I ran the following script to create the master key for one of the databases I created, but I was not开发者_运维百科 able to see they key in the node (see snapshot); does anyone know why? I look forward to hearing your reply, thank you.

USE AdventureWorks
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'
GO

You cannot see this information in the SSMS GUI, but you can see whether a database has a master key if you run the following:

SELECT d.is_master_key_encrypted_by_server
FROM sys.databases AS d
WHERE d.name = 'AdventureWorks';

Here are a couple methods of testing for the existence of the DMK.

Note that the [is_master_key_encrypted_by_server] column in sys.databases can show 0, but the DMK exists and has been dropped from being encrypted by the SMK.

I hope this helps.

======================

-- 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 = 'ljlLKJjs$2@l23je'
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 = 'ljlLKJjs$2@l23je'
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 = 'ljlLKJjs$2@l23je'
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 = 'ljlLKJjs$2@l23je'
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.