SQL Server Master Key

Question

I ran the following script to create a master key for one of the databases I created, but I could not see that they were in node (see screenshot); Does anyone know why? I look forward to your reply, thank you.

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

Answer 1

You cannot see this information in the SSMS GUI, but you can see if there is a master key in the database if you do the following:

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

Answer 2

Here are some testing methods for DMK.

Note that the [is_master_key_encrypted_by_server] column in sys.databases may show 0, but the DMK exists and has been excluded from the encrypted SMK.

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.