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.
What does this mean? 
If this comes from the Access side - it is going to be much more difficult to track down. If there are any Access databases that have some function or functionality built that locks out the users - that could be an update query, pass-through query or some code module behind a form.
The only way to identify the actual user is if the connection strings are setup to use Windows Authentication or each user has been granted a specific SQL login. If the connections from Access use a generic account - then you won't be able to determine the actual user.
If that is the case - then you are going to need to capture the connection information, and that is regardless of which method is used. You would need the Client IP address at a minimum and the ability to trace that back to the individual.
1 Like
A couple of people were concerned about the name of the table.
It is not the system users table sys.sysusers, i.e. not a microsoft-provided table.
It's a table named tblUsers in the user database. That is what controls access to the user app/db. The column booLocked is mysteriously getting set to True for all users.
1 Like
Is the database which has this table the same as the one used by your Access Application?
How does the access application make the connection to sql server? Does the application use a dedicated sql user name and password or an active directory user name and password?
Or does the application connect to sql server using the user name and password provided by the user that logged?
In your access application code,vba I assume, do you send in the currently logged in user's name when doing crud operations or does it use the application's dedicated username?
Do you have sql jobs that touch the application's backend? Which user does it use?
Do you have a dev or test version of the app and all other sql artifacts exist in dev test?
If you go back to the original trigger you posted, the real issue is that you're joining the user table to the INSERTED table.
from tblUser t
inner join inserted i on t.ANBRID=i.ANBRID
You have to remove that join. Just insert what in the INSERTED table into the audit table. This single join seems to be the root cause of all your trigger/audit issues.
This is the code for my trigger and it does work. It inserts a row into the audit table (when I change a row in SSMS). But the app gives error "invalid credentials" when a user tries to log into the app when the trigger is in-place.
create trigger trgAudittblUsers on tblUser
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]
,[LastEditBy]
,[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
It doesn't work as intended. there is absolutely no need to join to that table, could you explain why you are joining to that table, am I missing something?
Also a fix was provided for you here
to which you responded with "I fixed a data type issue, now my trigger works successfully." but not sure what you fixed.
This trigger isn't doing any comparisons to the table that it's auditing. At the very least, it's useless overhead. Have you tried the WITH EXECUTE AS OWNER thing on it yet?
1 Like
You can setup an extended event to look for anything involving this table. Also, sounds like this boolean flag is blocking users from using the app? Is the boolean flag set per user, or just one field for the entire app?