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?