I'm working with an older project. It was originally created as an MS Access front end and a Jet backend. The user names and passwords (security is not much of a concern here) were stored in plain text in a backend user table. Very simple system.
Later on, the backend was converted to MS SQL. They kept the same user table system. The front end has a single SQL login. So when the thing starts up, it logs into SQL right off the bat (the SQL credentials are just hard coded into the code) and then pops a login screen that accesses the user table I mentioned above. Again, very simple.
But now we have a bit of a problem. They want me to add some auditing capabilities for a few areas of the program. I've added the triggers needed and an audit table. No big deal, but I need the triggers to know who's logged in so it posts the correct user to the audit table. I don't want to create separate SQL users at this time. It impacts too much of the system for that to happen right now.
I thought a global temp table (1 column, 1 row) would work where I'd store the user but that's global to everyone, not just the connection because there's a single SQL login I'm guessing. It works, but it posts the most recently logged in user so it doesn't track things correctly. SO subsequent users overwrite that table each time. I read something about "context" info but it didn't seem like that was the solution either.
I've done a lot of searching in the last couple of days on how to handle this and I'm not coming up with anything that solves this problem. Any bright suggestions out there (other than creating and managing a load of SQL users and the front end tools for them to manage them)?
TIA,
AR.
Submit an article or tip