Hi experts,
If I create a trigger to audit inserts/changes/deletes on a specific table..... don't I have to also grant execute permissions to all users to the trigger as well as full permissions to the table I'm logging the changes into? Otherwise they will get "permission denied" errors, am I correct? Thanks
You can create the trigger so that it always runs under the authority of the person creating it and not of the person who happens to have modified the table. You do that by specifying "EXEC AS" when creating (or altering) the trigger.
2 Likes
Thanks, @ScottPletcher.. I'm the one creating the trigger so, as long as it will not reflect ME as the user corrupting the data, that will work. Don't want to set a trap for myself 
@ScottPletcher when I switch to "Audit both Failed and Successful logins"
does that require a bounce of the SQL service to go into effect?
I don't think so. If you're logging only Failed logins now, you can change the settings, wait a few minutes, and then check the log for successful logins being logged.
1 Like
It (the logging of a failed login) is working, Thanks
@ScottPletcher back to the trigger..... I've created it for UPDATE/DELETE. but in testing an update, no rows were written to the audit table. Does the database have to have TRUSTWORTHY = ON?
No .
I would not recommend setting that option - a better approach is to create an audit to capture what is needed. Enabling that option is going to put every successful login into the SQL log - making it much more difficult to find anything else in the log.
Thanks for the ideas. As for the trigger, I think it is designed correctly but it doesn't insert a row to the audit table when I change a row in that table. No error it just doesn't do anything.
And worse, our app users were not able to login with the trigger enabled. I think that''s because I had forgot to grant the user group EXEC permissions to the trigger, perhaps. We use database roles for app users.
We believe you but show us what you got as far as the trigger. are you sure there are no other triggers that might be conflicting?
@yosiasz I have checked and no other triggers exist. This is my trigger in the user db:
create trigger trgAudittblUsers on tblUser
WITH EXECUTE AS 'trg'
after update, delete
as
begin
insert into tblAU
([anbrID]
,[intCompanyID]
,[strFirstName]
,[strLastName]
,[strDepartment]
,[strUserID]
,[strPassword]
,[strTitle]
,[strTelephone]
,[strTelephoneExt]
,[strFax]
,[strCellPhone]
,[strEmail]
,[strWebEmail]
,[strUserType]
,[booLocked]
,[booAllowAP]
,[booAllowAR]
,[booAllowIV]
,[booActive]
,[booSuperUser]
,[booQO]
,[intCommodityLevel]
,[booQQLiteUser]
,[booFBA]
,[LandingPage]
,[PasswordExpirationDT]
,[ConsecutiveUnsuccessfulLogins]
,[CreateById]
,[CreateDT]
,[LastEditById]
,[LastEditDT])
select i.[anbrID]
,i.[intCompanyID]
,i.[strFirstName]
,i.[strLastName]
,i.[strDepartment]
,i.[strUserID]
,i.[strPassword]
,i.[strTitle]
,i.[strTelephone]
,i.[strTelephoneExt]
,i.[strFax]
,i.[strCellPhone]
,i.[strEmail]
,i.[strWebEmail]
,i.[strUserType]
,i.[booLocked]
,i.[booAllowAP]
,i.[booAllowAR]
,i.[booAllowIV]
,i.[booActive]
,i.[booSuperUser]
,i.[booQO]
,i.[intCommodityLevel]
,i.[booQQLiteUser]
,i.[booFBA]
,i.[LandingPage]
,i.[PasswordExpirationDT]
,i.[ConsecutiveUnsuccessfulLogins]
,i.[CreateById]
,i.[CreateDT]
,SUSER_SNAME()
,getDate()
from tblUser t
inner join inserted i on t.ANBRID=i.ANBRID
end
1, trg is a local login which has the db_owner role in the same db where the trigger exists.
2. I changed data in 1 row. No rows were inserted into tblAU
I have many audit triggers in production. Not a one of them has ever needed an EXECUTE AS on the trigger. Also, is YOUR ANBRID in the tblUser table?
2 Likes
Thanks @JeffModen
Yes, ANBRID is in the tblUser table
I used Executes as because for every table we create, we have to grant select, update, delete permissions to a db group for that table, otherwise they cannot access it. I think I'll need to do the same for the trigger, correct?
I think I'm close, just need to fine-tune it a bit. Any more tips?
I agree with Jeff, I've never had to use the Execute As statement in a trigger. Why are you joining to tbluser table anyway? Also, on delete, I don't believe there is an inserted table. The users need to have write access to this new table as well (I assume they do since this is an Update/Delete trigger)
1 Like
I think you need to fully read the documentation before you try something.
CREATE TRIGGER TR_UPD_tblUser ON tblUser
FOR UPDATE, delete
--NOT FOR REPLICATION
AS
BEGIN
insert into tblAU
([anbrID]
,[intCompanyID]
,[strFirstName]
,[strLastName]
,[strDepartment]
,[strUserID]
,[strPassword]
,[strTitle]
,[strTelephone]
,[strTelephoneExt]
,[strFax]
,[strCellPhone]
,[strEmail]
,[strWebEmail]
,[strUserType]
,[booLocked]
,[booAllowAP]
,[booAllowAR]
,[booAllowIV]
,[booActive]
,[booSuperUser]
,[booQO]
,[intCommodityLevel]
,[booQQLiteUser]
,[booFBA]
,[LandingPage]
,[PasswordExpirationDT]
,[ConsecutiveUnsuccessfulLogins]
,[CreateById]
,[CreateDT]
,[LastEditById]
,[LastEditDT])
select i.[anbrID]
,i.[intCompanyID]
,i.[strFirstName]
,i.[strLastName]
,i.[strDepartment]
,i.[strUserID]
,i.[strPassword]
,i.[strTitle]
,i.[strTelephone]
,i.[strTelephoneExt]
,i.[strFax]
,i.[strCellPhone]
,i.[strEmail]
,i.[strWebEmail]
,i.[strUserType]
,i.[booLocked]
,i.[booAllowAP]
,i.[booAllowAR]
,i.[booAllowIV]
,i.[booActive]
,i.[booSuperUser]
,i.[booQO]
,i.[intCommodityLevel]
,i.[booQQLiteUser]
,i.[booFBA]
,i.[LandingPage]
,i.[PasswordExpirationDT]
,i.[ConsecutiveUnsuccessfulLogins]
,i.[CreateById]
,i.[CreateDT]
,SUSER_SNAME()
,getDate()
from inserted i
END
2 Likes
Thanks @yosiasz.. I fixed a data type issue, now my trigger works successfully.
Question for everyone: Will I need to grant the user group execute permissions to the trigger?
** I found the answer to my question above. I need to grant update/insert on the audit table. No need
to grant exec on the trigger.
1 Like
It happened again today. Someone or some process, locked all users out of the app by setting a boolean flag in a table. We are logging both successful and failed logins but the login that did this may have logged in hours (or days) ago. Is there any way to determine a list of "suspects"?
Are you sure you really want to grant UPDATE on an audit table? Seriously??
Or frankly even INSERT. Do you want someone to be able to INSERT their own row(s) into your audit table?!
You could set up cdc on that table and/or use a trigger to log any change to that flag in real-time and notify you immediately when it happens.