Jana Sattainathan has to find and replace a lot of code:
The database has 100’s of stored procedures (700+ to be somewhat precise)
Qualifying stored procedures have these characteristics
- Procedure name ends with “_del”
- Procedure has the string “exec” in the code
- Procedure has the string “sp_execute” in the code
This is what needs to be done:
-
Replace CREATE PROC with ALTER PROC
-
Replace SYSTEM_USER with “ORIGINAL_LOGIN()”
-
Replace AS at the beginning of CREATE PROC with “WITH EXECUTE AS OWNER AS”
-
Comment out some SET statements
-
…in fact, there could be any number of other changes
Read on to see how Jana did it.