[Code] Using EXECUTE AS with a trigger to capture changes when a user doesn't have permissions.

Had to build this to keep track of changes on certain tables in our environment.  Due to limited permissions, we wound up with people hitting the table but the trigger not working right, because they hadn't been granted permissions.  So the obvious solution is to grant permissions to both tables to a group, then add people into that group.  Not an option for this particular scenario.  Hence, my code.

Note that THIS WILL NOT WORK across databases, unless it’s marked as TRUSTWORTHY.  Planned functionality, not a bug. Within a database it works. 

New code bolded.  I created a user Change_User, a change-tracking table named change_mytable and gave Change_User select/insert permissions ONLY into change_mytable.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

alter trigger [dbo].[mytrigger]

on [dbo].[mytable] with execute as 'change_user'

after update

as

BEGIN

  SET NOCOUNT ON

  declare @current_user nvarchar(128)  --suser_sname() is nvarchar(128)

  execute as caller                    --caller is the original person running code

  set @current_user = SUSER_SNAME()    --get the name of the user

  --select @current_user               --returns my name when I run it

  revert                               --go back to the EXECUTE AS user, ch_user

  --Audit capture

      if exists (select name from sysobjects where name = 'change_mytable')

      begin

      --print suser_sname()                     --returns change_user

      insert into change_mytable(

      ID, name, updated_date, change_login, dml_action)

      SELECT

            ID, NAME, getdate(), @current_user, 'Update'

            FROM Deleted

      end

END

GO