SQL SERVER EXECUTE AS CLAUSE

The EXECUTE AS clause is used to specify the security context under which the stored should execute. DBA/Developer can specify the following values in the EXECUTE AS clause:

SELF: The stored procedure executes under the security context of the current user.

OWNER: The stored procedure executes under the security context of the user that owns the procedure.

CALLER: The stored procedure executes under the security context of the user calling the procedure. To execute the stored procedure successfully, the user calling the stored procedure would require permissions on the stored procedure and any underlying database objects referenced by the stored procedure. user_name: The stored procedure executes under the security context of the specified user, regardless of which user called the stored procedure.

The ENCRYPTION clause encrypts the CREATE PROCEDURE statement used to create the stored procedure. This ensures that the statement is not stored in plain text that is readable by others. When a stored procedure's definition is encrypted, users cannot use the sp_helptext system stored procedure to view the stored procedure's definition. With the given CREATE PROCEDURE statement, if DBA/Developer attempted to use the sp_helptext system stored procedure to access the definition of the stored procedure, DBA/Developer would receive the following

output: The text for object 'dbo.usp_UpdateSalesHistory' is encrypted.In addition, users cannot view the definition of the stored procedure by querying the sys.sql_modules catalog view or using Visual Designer in SQL Server Management Studio. In this scenario, if a user queried sys. sql_modules using the following SELECT statement, a definition value of NULL would be returned:

SELECT definition

FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.
usp_UpdateSalesHistory');

DBA/Developer should not use the CREATE PROCEDURE statement that omits the EXECUTE AS clause. If DBA/Developer omit the EXECUTE AS clause when creating a stored procedure, then by default the stored procedure will execute under the security context of the caller. This statement also includes the RECOMPILE clause, which will cause the stored procedure to be recompiled each time it is called. DBA/Developer can use the RECOMPILE clause to force stored procedure compilation, but this was not required in this scenario. DBA/Developer should not use the CREATE PROCEDURE statement that specifies a user name in the EXECUTE AS clause because this will cause the stored procedure to execute under the security context of DevUser. DBA/Developer should not use the CREATE PROCEDURE statement that includes the EXTERNAL NAME clause because this statement will generate a syntax error. The EXTERNAL NAME clause is only valid when creating a CLR stored procedure. A CLR stored procedure is a stored procedure written in a .NET Framework language. To create a CLR stored procedure, DBA/Developer use the .NET language and define a static class method. Then, DBA/Developer compile the class, register the assembly in SQL Server with the CREATE ASSEMBLY statement, and use the CREATE PROCEDURE statement in SQL Server to reference the assembly. The EXTERNAL NAME clause must be specified in the CREATE PROCEDURE statement to reference the appropriate method.