Set Options in Stored Procedures

You can use almost all of the set command options inside stored procedures. The set option remains in effect during the execution of the procedure, and most options revert to the former setting at the close of the procedure.

Only the dateformat, datefirst, language, and role options do not revert to their former settings.

However, if you use a set option (such as identity_insert) that requires the user to be the object owner, a user who is not the object owner cannot execute the stored procedure.