azureazure-sql-databaseencryption-symmetricazure-sql-serverazure-mysql-database

Can 'Temporary Symmetric Keys' be dropped in the same session which created it in Azure SQL?


Even after closing a connection temporary symmetric keys were not deleted from tempdb.sys.symmetrickeys. I want to understand if I can always drop them in the same session without worrying about permissions ?


Solution

  • As I tried it appears that a temporary symmetric key is only accessible in the same session that it was created. Usually they should be cleared when the session is ended, but if you create a temporary table from a stored procedure, it will be applicable to that stored procedure only. And it will be dropped once procedure is over. This is similar to a table created within a batch of dynamic SQL.

    If you you have used either of the above and then created the keys, they will continue to exist even after the tables are dropped or procedure is completed.

    Since you cannot use this key again in another session, you can go ahead and drop them safely. This requires CONTROL permission on the symmetric key. See an example from MS doc.