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
