question

aRookieBIdev avatar image
aRookieBIdev asked

Stored Procedure Change Tracking

Hi All, I need to identify stored procedure changes (i.e altered by anyone) through DML triggers even if the stored procedure is encrypted. Please let me know any useful tips references to start with. Thanks, Kannan
sql-server-2008stored-procedurestriggerencryptiondml
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'm not sure I quite follow what you are trying to accomplish; can you clarify - Do you mean changes to the SP itself? If so, a DDL trigger may be more appropriate - to do something when an ALTER PROC statement is issued? http://technet.microsoft.com/en-us/library/ms186406(v=sql.105).aspx Something similar was discussed at http://stackoverflow.com/questions/1521598/how-to-get-procedure-text-before-alter-from-ddl-trigger Or do you mean to identify what *data* the SP changed (Updates/inserts/deletes)?
0 Likes 0 ·
i need to capture stored procedure changes done by any user.The stored procedure is also encrypted in this case.
0 Likes 0 ·
Do you need to see what was altered (old vs new procedure text), or simply that the proc was altered in some way?
0 Likes 0 ·
ideally old vs new , but the challenge i guess would be is to get the encrypted storedprocedure
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
Personally, I wouldn't use triggers for this. Instead, I'd use extended events. You have events for object_altered, objected_created, objected_deleted. You can capture every modification to the object as it occurs. If you need to, you can add the global event for sql_text. I'm pretty sure in this case that will capture the new code (although not for encrypted procs). Plus, with extended events you get very good, low level filtering, you can send information to files or to the ring buffer where you do other things based on the events fired.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thanks for the quick response. Will Triggers work for encrypted Sps?
0 Likes 0 ·
Define work. If you mean will it let you look at the code? No. But encrypted procs are very easily unlocked. Do a search on the web. It's not a real encryption like what they do with data.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.