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.
Now, what if I go to another database, say the Finances database. I see nothing.
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.
This instance has been used with TDE, so if I go to master, I’ll get this:
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 – https://msdn.microsoft.com/en-us/library/ms189446.aspx
CREATE MASTER KEY – https://msdn.microsoft.com/en-us/library/ms174382.aspx
Connect Item to add a flag – https://connect.microsoft.com/SQLServer/feedback/details/3118588