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
SET QUOTED_IDENTIFIER ON
alter trigger [dbo].[mytrigger]
on [dbo].[mytable] with execute as 'change_user'
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
if exists (select name from sysobjects where name = 'change_mytable')
--print suser_sname() --returns change_user
insert into change_mytable(
ID, name, updated_date, change_login, dml_action)
ID, NAME, getdate(), @current_user, 'Update'