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