2

Is there a query to see which stored procedures are using a specific symmetric key? Or a built in feature of SQL to see this information. The stored procedures are using:

OPEN SYMMETRIC KEY !@#$%^ WITH DECRYPTION BY CERTIFICATE !@#%^&**^ 'string'
Solomon Rutzky
  • 68,731
  • 8
  • 152
  • 292
Jason C
  • 33
  • 2

1 Answers1

0

While I don't know of a solution that solves your specific problem, you can search the raw source code of your stored procedures using the sys.sql_modules view. This, however, won't work with encrypted (i.e. WITH ENCRYPTION) procedures.

SELECT s.[name]+'.'+o.[name]
FROM sys.procedures AS o  -- or sys.objects for all objects
INNER JOIN sys.schemas AS s ON o.[schema_id]=s.[schema_id]
WHERE [object_id] IN (
    SELECT [object_id]
    FROM sys.sql_modules
    WHERE [definition] LIKE '%symmetric%' -- search criteria goes here
    );

Important: The query above will return all stored procedures containing the text string "symmetric", not just procedures that use symmetric encryption or keys. Remember that it will also match for instance text that is in comment blocks och quotes.

Daniel Hutmacher
  • 9,113
  • 1
  • 25
  • 52
  • That search was just what I needed, the sp was not encrypted itself by symmetric key but the information it gathers is. I guess this could be an add on question then........Once all stored procedures are found is there also a query that could change the symmetric keys and certificates to all of the sp's at once OPEN SYMMETRIC KEY PasswordFieldSymmetricKey DECRYPTION BY CERTIFICATE WDPasswordFieldCertificate to OPEN SYMMETRIC KEY PasswordFieldSymmetricKey2 DECRYPTION BY CERTIFICATE WDPasswordFieldCertificate2 – Jason C Jan 21 '16 at 11:53