Using Execute As with Stored Procedures and Dynamic SQL

Category : Tips User Rating : 5 Stars      Views : 3.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




In most situations it is sufficient to grant a user ‘EXECUTE’ rights to a stored procedure in order to run it. However if the stored procedure contains dynamic SQL an error can occur even if the user has sufficient rights to run the stored procedure. The error will probably be similar to the following :
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Employee', database 'SQLMatters', schema 'dbo'.
Msg 229, Level 14, State 5, Line 1
The UPDATE permission was denied on the object 'Employee', database 'SQLMatters', schema 'dbo'.
Of course this can be resolved by giving the user SELECT and UPDATE rights on the underlying table (in this case the ‘Employee’ table). However it may be undesirable to give the user this level of rights, after all one of the reasons for using stored procedures is to ensure that users can only access data via the controlled interface of the stored procedure.

To resolve this conundrum it’s possible to specify that when the stored procedure is run it is done so under the context of a different account. This account is given the required access to the underlying tables. This then allows the stored procedure to run dynamic SQL without allowing the user direct access to the underlying tables. To specify that a stored procedure is required to run under a different account we can use the ‘EXECUTE AS’ clause :
CREATE PROCEDURE usp_UpdateEmployeeColumn @KeyValue VARCHAR(50)
WITH EXECUTE AS 'DynamicSQLUser'
AS

.
.
.

GO
For simplicity I’ve not included the logic within the stored procedure, but the key here is the EXECUTE AS clause which specifies that the stored procedure will be executed under the security context of the user ‘DynamicSQLUser’. We can then give that user SELECT and UDPATE rights to the Employee table :
GRANT SELECT ON Employee TO DynamicSQLUser
GRANT UPDATE ON Employee TO DynamicSQLUser
One important point to make is that this does *not* give the user IMPERSONATE rights, so will not give him any of the underlying rights of the DynamicSQLUser user. Whilst digressing it’s also worth checking whether dynamic SQL is actually required as there may be a better way of resolving the problem without the use of dynamic SQL.

A final point to be aware of is that functions such as SUSER_NAME() return information relating to the current user, which in the case of our stored procedure is now always going to be DynamicSQLUser. However it’s fairly easy to amend the stored procedure code to return the actual user using the EXECUTE AS CALLER and REVERT statements as follows :
CREATE PROCEDURE usp_UpdateEmployeeColumn @KeyValue VARCHAR(50)
WITH EXECUTE AS 'DynamicSQLUser'
AS

SELECT SUSER_NAME() -- returns 'DynamicSQLUser'

EXECUTE AS CALLER
SELECT SUSER_NAME() -- returns actual caller of stored procedure

REVERT
SELECT SUSER_NAME() -- returns 'DynamicSQLUser'

GO
This last example is taken almost directly from SQL Server Books Online. For further reading Erland Sommarskog has written an excellent article at : http://www.sommarskog.se/grantperm.html#EXECUTE_AS

Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/Using Execute As with Stored Procedures Containing Dynamic SQL.aspx

Keywords

SQL,Dynamic SQL,TSQL,Execute As,Impersonate


Comments

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered