Permission error when running dynamic SQL but not when using a normal statement

116 Views Asked by At

I have a stored procedure that gives a permissions error when trying to do an UPDATE using dynamic SQL under user HandheldServiceAcct.

CREATE PROCEDURE [dbo].[spHandheldTest] 
AS
BEGIN
    DECLARE @sql nvarchar(4000) = 'UPDATE [TableName] SET [Order Quantity] = 1 WHERE [ID] = 1' 
    EXEC sp_executesql @sql 
END

I then execute it like this:

execute as login = 'HandheldServiceAcct';
exec spHandheldTest

but I get this error:

The UPDATE permission was denied on the object 'TableName', database 'DB', schema 'dbo'.

The same UPDATE statement works when I don't use dynamic SQL.

Why is this? What would I have to do to allow this dynamic UPDATE to work?

0

There are 0 best solutions below