Answer

Contents

Question

?How do you create a rollback script when replacing a stored procedure?

?Answer

To create a backup of the stored procedure that is being replaced for rollback purposes use the following steps prior to replacing the stored procedure:

1. Within the SQL Server Management Studio go to: Object Explorer --> Databases --> [Database hosting the stored procedure]? -->?Programmability--> Stored Procedures.
2. Right-click on the stored procedure to be backed up.?
3. From the context menu select Script Stored Procedure as.
4. Select DROP and CREATE to.
5. Select File ....?
6. Save the stored procedure creation script.
7. Grant permissions to the creation script so TLADMIN and TLUSER users can run the script by adding the following lines at the end of the script:

GRANT EXECUTE ON [dbo].[REPLACE WITH STORED PROCEDURE NAME] TO TLADMIN
GO
GRANT EXECUTE ON [dbo].[REPLACE WITH STORED PROCEDURE NAME] TO TLUSER
GO

If rollback of the stored procedure is needed run the newly created script.

?Applies to version(s): ? All SQL Server-based versions