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.
It's not an audit table, @ScottPletcher. The boolean flag column booLocked, is in a users table the app references to determine who can use the app.
I was able to create a trigger on the table. HOWEVER, when the trigger is in-place, users cannot login to the app, they get error "invalid credentials". So I had to remove the trigger unfortunately. The trigger works but the app doesn't play well with it.
I guess for triggers that modify audit tables, I've had to use EXEC AS on the trigger because I explicitly DENY everyone else access to the audit tables. It seems I'm a lone wolf on that, although it's the only way that makes sense to me.
You could DENY any modifications of the column to users.
Of course you'd need to allow the app to modify it. If you have a separate login/userid that the app runs under, that should be doable. If the app runs under each user's individual id, then it will be more complicated.
You keep saying things in a generic fashion such as "a table". If you mean your users table, you have a column that captures the SUSER_SNAME() and the date. Did that NOT capture the event?
@JeffModen Specifically, the table within the application database... contains column booLocked. That columns value is mysteriously getting changed, at random times to 1/True for all users. Then no one can log into the app until I reset the values.
"you have a column that captures the SUSER_SNAME() and the date. Did that NOT capture the event?" Sorry, I don't understand the question.
Thanks
Well you do have some users as db owners right?
Ok... I read the whole chain of events you posted. It's a bit confusing because of all the "generic" talk. You just did it again. "Specifically, the table". That's not specific at all. What is the name of the table?
It also looks like that whatever the name of "the table" is, you don't have a trigger that's currently active because your users get errors because they apparently don't have rights to "the table" through the trigger.
So it looks like we were wrong about not having to grant any privs. I would NOT do so by granting privs using a specific human user. Instead, try using WITH EXECUTE AS OWNER option in the definition of the trigger and see if that works for users.
1 Like
I think you need to take a step back - and consider what it is you are really trying to accomplish. From what I gather - you have an update being performed on a table mysteriously. At some point in time - a column in that table is changed and that change is locking the users out of the system.
After that occurs - you have to unlock those users by executing a script to change that value.
The first thing I would do is download and install SQL Search - then I would search all code in all databases on that instance for that column. I would pay attention to any SQL Agent jobs first - as this smells like an automated process - something that locks users based on some logic - which probably isn't working as it should.
The next thing to focus on is any code that updates that column - checking for code that may be related to locking users, specifically any code that affects multiple users.
Now - if you find something you can then setup an extended trace to capture any time that code is called. After the event happens, check the extended event trace to see if you captured the offending process.
If you don't find anything - you can still setup an extended event. Only that event will have to be filtered based on the text including that column - and hopefully that captures the information needed to track down the process/user.
Last resort for me is a trigger - and if I do have to put in a trigger, then it would include UPDATE() or COLUMNS_UPDATED(), checking for that specific column being included in the update statement. Then capture the specific information only - the user executing the update - the date/time - the inserted value (new value) for the booLocked column, etc.
But doesn't that assumes everything is setup to capture the current user in the code right?
CODE
If the app runs using a service account or a sql user and nowhere in the code it captures currently logged in user to update the audit columns = no way to know who did what
TRIGGER
Same here right? if the trigger is not currently logged in user aware, would not capture who did what
TRACES
Not sure what this can capture either if there is nothing in the code that specifies currently logged in user?
I your app was not designed and developed with auditing in mind, it would be very hard to suddenly implement it or do forensics on it?
"What I'm trying to accomplish ....." Who or what job/process is changing the bit flag. The table is not a microsoft-shipped table. It's named "tblUsers" and resides in an application database.
Thanks for the other tips. It may be coming from the Access side. I'll try SQL Search.