Do I have a Database Master Key in a database? #SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

How can I tell if I have a Database Master Key in a database? It’s actually easy. I query the sys.symmetric_keys DMV for data. If I get a result that has a name of ##MS_DatabaseMasterKey##, then I have a database master key.

You can see this below. I’ve queried this DMV in my Sandbox database.

2017-01-11 12_21_55-SQLQuery1.sql - (local)_SQL2014.Sandbox (PLATO_Steve (59))_ - Microsoft SQL Serv

Now, what if I go to another database, say the Finances database. I see nothing.

2017-01-11 12_22_34-SQLQuery1.sql - (local)_SQL2014.Finances (PLATO_Steve (59))_ - Microsoft SQL Ser

Let’s add a master key here and then query. Note, I am not disclosing the real password here. Never do this, even in test systems.

2017-01-11 12_23_14-SQLQuery1.sql - (local)_SQL2014.Finances (PLATO_Steve (59))_ - Microsoft SQL Ser

This instance has been used with TDE, so if I go to master, I’ll get this:

2017-01-11 12_24_20-SQLQuery1.sql - (local)_SQL2014.master (PLATO_Steve (59))_ - Microsoft SQL Serve

You can see that I not only have a DMK, I have a Service Master Key (SMK), which protects the instance.

When I create my DMK, the only parameter I can provide is a password, after the optional “ENCRYPTION BY PASSWORD” keywords. I don’t name it, so I can count on the naming being fairly consistent. I don’t think that the name would change from version to version, but it could.

I’d prefer that MS not create magic numbers or names, and instead, add a column to the DMV that denotes this is a DMK.


sys.symmetric_keys –


Connect Item to add a flag –

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.