Drop Master Key keeps on running
I am trying to drop master key of the database, but everytime I run the below sql
DROP MASTER KEY
The query keeps on executing and never completes.
Whenever I issue a command that should finish instantaneously but doesn't, blocking comes to mind. So I'll note the SPID of the window where I issued the command and then select * from sys.dm_os_waiting_tasks where session_id = @spid and resolve whatever the block is.
Do you have "Control" permissions on the database? If so, are there any private keys in the database? Have they been removed prior to performing this operation?
I could repeat that only by 2 query windows. In one:
create master key ENCRYPTION BY PASSWORD = 'password' begin tran --!!! OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
The second window:
drop master key -- waits...
So maybe someone is using that master key! You should probably kick all other users from db and try then.. Or find out who is using it (yourself?).
So I got it working after disabling Service Broker.