Returning current user from SQLServer

[Robman]

I have converted my Access 2007 back end to a SQLServer and am trying to
return the current user back to the application. Unfortunately if I use the
built in function CurrentUser is only returns Admin which is from Access and
if I try to use the TransactSQL function CURRENT_USER in a SQL Docmd.RunSQL
statement (Select CURRENT_USER) Access errors and says it is looking for a
SELECT, INSERT etc type of statement. If I run 'Select CURRENT_USER Go' in a
SQL Query analyser window it returns the logged in user name.
Does anyone know how to get Access 2007 to tell me the name of the user that
is logged onto the backend (SQLServer) database?
Thanks

 

[Douglas J. Steele]

I don't believe it's possible to use RunSQL for T/SQL statements. Access has
no way of knowing that you're trying to connect to an external data source.
As well, as you've already discovered, RunSQL can only be used with Action
queries (INSERT INTO, UPDATE, DELETE)

You'll have to write a pass-through query, then open a recordset based on
that pass-through query to retrieve its response.

 

[Robman]

Douglas:
Thanks very much for the advice!
It seems very strange that MS has removed User level security from Access
but has provided no easy way to find out who the current user is when working
on SQL Backends! As you can imagine without user level security it is
relatively difficult to secure individual things in a database.
Anyway I will try to figure out how to do this but so far it has me stumped
and I don't know if a pass through query will do the job as there is only one
value to be returned in each case!
Thanks a lot!
Robman

 

[Robman]

Chris the information I have came straight out of the MS Access help files
for 2007

"Cause
User-level security is not supported by the new file formats in Microsoft
Office Access 2007."

Hence the reason I am trying to get the user name from the SQLServer back end.

 

[Robman]

Unfortunately that is not what I read the message to mean. My understanding
was that if you wanted to use the new capabilities of Access 2007 then you
had to save your db in the new format - accdb as opposed to the old mdb.
Thanks for your post.

 

[Rick Brandt]

Unfortunately that is not what I read the message to mean. My
understanding was that if you wanted to use the new capabilities of
Access 2007 then you had to save your db in the new format - accdb as
opposed to the old mdb. Thanks for your post.

Some of the new features do require that.

Implementing user level security just so you can capture user's names would
be phenominal overkill. Just use the API call to return the current windows
account name.

 

[Rick Brandt]

He was also interested in securing "individual things in a database".

With a SS back end data should be secured with SS security. If he wants to
use code to decide what users can do in the front end getting the user name
from SS or the Windows API is still a better option than going through all
the guff to set up Access ULS particularly if he wants to use the new AccDB
features.

 

[Robman]

Rick (and Chris):
Thanks very much for your posts on this subject.
Just to clarify:
I have secured the data in SS using OS based security and roles.
I am attempting to create some "smart" menus that display only what is
appropriate for the user signed in.
I have figured out how to use a Pass Through query to get the user name back
- this comes with some problems because SYSTEM-USER returns both the username
and the PC name i.e. PCNAME/Username instead of just user name and to be
honest I can't remember how to use the StringLeft,Mid,Right funcitons to get
rid of the PC name and the slash (/) SO having said that is there a way
within Access to "call" the API on the server to get just the user name that
is logged onto the Access session?
Not being a "programmer" it takes a while to learn each of these things one
by one so I really appreciate all the help that both of your are providing!
Thanks
Robman

 

[Robman]

It worked just fine - thank you!
The only problem I have run into is how to make the username variable
"global" or "public" so that it persists throughout the session.
I know that each time I want to user the user name I could call the function
again but what I tried to do, unsuccesfully, was to declare the variable as
public so that it would be available at all times (once read).

The way I attempted to do this was in a declaration:
Public loggeduser as string
loggeduser=fOSUserName

Even though I declared loggeduser as a variable length string I received an
error code "Invalid attribute in Sub or Function" and the "Public" was
highlighted.
A thorough re-reading of the Public function did not clarify why the
statement was invalid so hopefully somebody knows!

Perhaps there is a better way to accomplish what I want to do other than try
and make loggeduser a persistent variable?

Thanks for all your help so far!
Robman

 

[Robman]

Chris:
No doubt two heads are better than one!
Thanks for the suggestion - that should get the job done - what I will do is
since I have put the user name on the opening form I will just leave that
form open behind everything else and reference it!
Thanks for all your help!