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'
0

1 Answer 1

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.

1
  • 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, 2016 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.