Script Download: The script is available for download in Microsoft Script Browser for Windows PowerShell ISE. You need to install the Script Browser application first, and search for the script sample title.
This script can be used to detect if the database master key is not encrypted by Service Master Key.
SQL Server uses a hierarchy of keys and certificates when a database is enabled for Transparent Database Encryption [TDE]. At the upper levels of the hierarchy, the Service Master Key [SMK] encrypts the Database Master Key [DMK] for the master database. The Database Master Key [DMK] encrypts and protects the Certificate and Database Encryption Key [DEK] involved in the encryption of the user database. After enabling a user database for TDE, you will be able to remove the SMK encryption of the DMK for the master database. If you restart the SQL Server at this point, the database startup will encounter error 15581 and prevent the database from starting up. In some situations, the LogWriter will hang with a wait_type of WRITE_LOG and prevent any transactions from committing in this database. Even though it is possible to remove the SMK encryption for the master database DMK, it is not recommended to do this.
You can find more All-In-One Script Framework script samples at http://aka.ms/onescriptingallery