Referencing sql logins from access
-
Hi,
I'm developing an access database with an sql server backend. The sql connection is set up for sql logins , but I can't find a way of referencing the current user. I have tried the CurrentUser() method but this just returns "Admin" every time no matter who logs in. Any ideas?
-
You might want to create a simple stored procedure that calls the suser_sname() function in SQL Server. The Access CurrentUser function will only work if you have turned on the security in Access itself. Since it sounds like you have not done that it will always return Admin as that is the default.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Hi citrustrip,
quote:
I'm developing an access database with an sql server backend. The sql connection is set up for sql logins , but I can't find a way of referencing the current user. I have tried the CurrentUser() method but this just returns "Admin" every time no matter who logs in. Any ideas?as Gary said, creating a simple stored procedure will be the easiest way.
However, if you want to turn on Access security mechanisms, you have to cope with workgroups, the system.mdw ?!? file and the workgroup administrator programm which comes with Access. Well, in fact security mechanisms are always turned on in Jet, but as long as you leave the admin password blank they remain invisible. Access security is all about workgroups. I guess right now, your workgroup file contains two groups (admin and users, by default) with one predefined user account (Admin, which is what you see).
So, when you start Access, the Jet Engine tries to log on as Admin with blank password. And only if this fails, you will be prompted for a password.
If I remember right, you can use a special startup option in Access to log on as a different user.
MSACCESS.EXE /User Bill /Pwd Gates
should work, when you have a user Bill defined. Note, that this is anything else but secure.
And again, if I remember right, Access security does not work on a per database basis. It's an all or nothing approach.
HTH
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
If you don't want to implement security in Access, you can get the windows-login of the user. In these cases I prefer using an API-call:
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" ( _
ByVal lpBuffer As String, nSize As Long _
) As Long
Public Function UserName() As String
Dim sBuffer As String
Dim lSize As Long
sBuffer = Space$(255)
lSize = Len(sBuffer)
Call GetUserName(sBuffer, lSize)
If lSize > 0 Then
UserName = Left$(sBuffer, lSize - 1)
End If
End Function
Erik
-
Thanks for the advice,
The API approach is great, I'd have never worked that out. Although, I have managed to achieve what I want by creating a new column in the table and setting the default value to suser_sname(), this means that whenever a new record is created the user who created it is automatically stored.
Nice one,
Ben.
-
Hi Ben
quote:
Although, I have managed to achieve what I want by creating a new column in the table and setting the default value to suser_sname(), this means that whenever a new record is created the user who created it is automatically stored.well, this is obviously the most easy one to implement.
Sometime one sees the complicated, but miss the obvious one
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply