ansaurus

Question

How can I get the username from a MS Access front end MDW in a SQL back end audit table?

Answer 1

+2 A:

From Access VBA you can use the CurrentUser() function to return the MDW user name. You need to find a way to tell SQL Server about that name. If you're building and submitting the DML statements from Access, you could add the CurrentUser value as a field expression.

I'm curious about using both Access user level security and SQL Server authentication. At first blush it sounds like a "belt and suspenders" approach ... except that SQL Server can be a very effective belt, while Access user level security is a comparatively ineffective set of suspenders. I would question what benefit ULS adds to your application.

Consider discarding ULS and switching to Windows Authentication for SQL server. That could be a simpler, cleaner, and more secure approach.

HansUp 2010-09-02 20:51:38

He may need to secure the Access objects: forms, reports, etc. and not just data.

Jeff O 2010-09-02 21:00:22

What I was thinking was that I could put the hostname and currentuser() in a temp table linked in SQL. I'm using SQL 2008, so if I look in sys.sysprocesses I can find the SPID and hostname where the program_name = 'Microsoft Office 2003'. If I could find the SPID of the process doing the insert/update/delete trigger then I can try to tie the hostname to to the SPID and through my temp table, back to the access username. I just don't know how to find the SPID of the process doing a insert/update/delete in the trigger.

KFleschner 2010-09-02 21:22:25

Securing Access objects in the front end can be better accomplished by just distributing an MDE, which makes them uneditable. It's unlikely that you'd need to distinguish every user's permissions to use objects in the front end by Jet user. That is, you could easily design your security on your front end with two users, admin (for everybody) and another user (the one who can make changes).

David-W-Fenton 2010-09-03 15:47:01

I definitely agree with @HansUp that there's likely very little benefit to using granular Jet ULS on the front end. Even if you continue to do that, why not record your audit trail using the Windows logon? Isn't that sufficient? Yes, it requires using Windows authentication, but that's much easier than SQL authentication, anyway.

David-W-Fenton 2010-09-03 15:49:07

The tables on the front end are linked using a system DSN, so to the SQL server it's as if there are several instances of the same user logged in when it's acutally many different users using the FE on different machines. On top of that, the Access security is used to define an additional level of custom permissions inside the FE for operations (e.g. only QA people can do action x). Yes, one way would be to move everything to Windows authentication, but that would be a big change from what has been done already.

KFleschner 2010-09-03 16:33:40

"Several instances of the same user" is only because you're using SQL Authentication, or because you're specifying the same username/password for all the users. If you use Windows Authentication, you don't have to store any user logon information in your DSN -- the user gets authenticated as themselves automatically by SQL Server, which gets the info. from Windows/AD.

David-W-Fenton 2010-09-03 22:29:26

It may be a big deal to move to Windows Authentication, but it would vastly simpify what you're trying to do. Also, you have to realize that Jet ULS is on the way out, as it's not supported with ACCDB format, which is where MS is putting its development efforts. Many new features are only available in ACCDB, and that includes front ends. I would get together with your network sysadmin and plan for a method of allowing certain administrative users to manage NTFS users so you could have custom groups defined for the purpose of this particular database.

David-W-Fenton 2010-09-03 22:31:15

Answer 2

A:

I bet @@spid in the trigger works because it is executed by the process doing the DML.

Just be aware that this may not always be reliable because sometimes Access opens additional connections without having any way of running your special code to log the user against the spid in use.

Update

Have you considered using the CONTEXT_INFO variable that is specific to each SQL Server session?

DECLARE @Info varbinary(30)
SET @Info = Convert(varbinary(30), 'My Username')
SET CONTEXT_INFO @Info
SELECT Left(Convert(varchar(30), CONTEXT_INFO()), CharIndex(0x0, CONTEXT_INFO()) - 1)

This may mean hitting a table behind the scenes anyway, but it's surely going to be faster than doing it yourself.

Emtucifor 2010-09-03 03:42:56

Thanks for reminding me of @@SPID. I had a mental margarita on that one.The solution was to create a table in the server to store the hostname and username in Access. The main form open event inserts the hostname from VBA.Environ("COMPUTERNAME") and CurrentUser() into this table. In the audit trigger I find the username with Select @UserName = dbo.AccessUsers.Username FROM sys.sysprocesses INNER JOIN dbo.AccessUsers ON sys.sysprocesses.hostname = dbo.AccessUsers.HostName WHERE (sys.sysprocesses.program_name = 'Microsoft Office 2003') and sys.sysprocesses.spid = @@SPID

KFleschner 2010-09-03 15:10:28

That's great! Is there any way you can store the spid instead of look up the user name real time, and later convert it to the username based on the spid and the date/time (using a table with a history of the current user)? The way you're doing it is fine, it's just best to do as little as possible in a trigger...

Emtucifor 2010-09-03 16:28:08

I would think that might get messy with users logging in and out. You'd get a large table of repeated information. You'd have to log the time to a table, then trigger the access table as well to find the SPID in SQL or do the insert on the Access side with ADO. I'm with you on having triggers do the smallest amount of work, but I'm thinking it'd be cleaner in the long run to have the trigger find the username. I'm all ears if you can think of a better way of doing it.

KFleschner 2010-09-03 18:03:57

There is a special variable that you can store data in that's specific to the session... let me try to find this. Found it! See update.

Emtucifor 2010-09-03 20:27:08

ansaurus

tags:

views:

answers:

How can I get the username from a MS Access front end MDW in a SQL back end audit table?

related questions