[SOLVED] How to execute a SQL Server stored procedure containing an ALTER statement from a database front end without granting it ALTER permissions?

How to execute a SQL Server stored procedure containing an ALTER statement from a database front end without granting it ALTER permissions?

Database Setup: I am using an MS Access front end connected to a SQL Server back end. For security purposes, the objects (views and stored procedures) used by the front end (user=MyDbsFront - names have been changed) have a different schema (schema2) then that of the database owner (schema1) which house the data tables. Data access is selectively provided to MyDbsFront via views or stored procedures (via ownership chaining).

One of the tables (schema1.MyTable) has a trigger set that updates the date in the Updated and UpdatedByUserId columns when a value in a row changes. I put this functionality on the back end as I sometimes manually upload data (via SSMS) and I want to make sure these columns are correctly updated regardless of the manner in which the table is updated. The front end does not have access to edit these two columns directly.

AutoPopulate Procedure: Recently, I created a stored procedure (schema1.AutoUpdate) that automatically populates some of the columns. When a row is modified using the procedure, I want to insert an ID representing "Auto" instead of the current user's ID into the UpdatedByUserId column. To prevent the update trigger from reverting this change, I include an ALTER statement to temporarily disable the trigger and then reenable it once the change has made.

Problem:. The procedure works fine when executed from the back end. However, when I attempt to execute the trigger from the front end, I get a permissions error ("Cannot find the object because it does not exist or do not have permissions..."). I have narrowed the issue to a permissions problem associated with the ALTER statement and if I grant ALTER permission to the MyDbsFront user on schema1.MyTable, everything works. However, I would prefer not to grant ALTER permission indefinitely to the front end as this introduces security liabilities.

Question: Is there a way to execute a stored procedure containing an ALTER statement from a database front end without granting it ALTER permissions?

I have tried using code in the procedure to temporarily grant the alter permission and the revoke it but it failed as you cannot grant permissions to the db owner or yourself.

Solution

Configure the stored procedure to "EXECUTE AS OWNER". The default is to "EXECUTE AS CALLER", and the caller lacks the permissions to disable the trigger.

create or alter procedure usp_foo
with execute as owner
as
begin
  set xact_abort on;
  begin transaction;
  disable trigger tg_tt on tt;

  insert into TT(UpdatedBy) values ('Auto');

  enable trigger tg_tt on tt;
  commit transaction;
end

The transaction is important for two reasons. First it ensures that the trigger is re-enabled in case of a failure. And second it locks the target table so no other session can insert rows while the trigger is disabled.