Capture SQL Server logged in db User name in Access 2007 Accdb

15 pts.
Tags:
Access 2007
Access 2007 security
Microsoft Access
SQL Server 2005
SQL Server 2005 security
SQL Server security
I have a SQL Server 2005 backend with an Access 2007 Accdb front-end application.  Security has been established via SQL Server database user groups with associated table-level permissions.

I would like to disable certain form functionality based on the logged in user.  Is there a way to "capture the SQL Server logged in user name" in the Access code?  Probably in the form_load/open event.



Software/Hardware used:
SQL Server 2005, Access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

The SQL Server system function suser_sname() will return the username of the login used to connect to the SQL Server. Wouldn’t it be easier to have Access provide the NT Username of the person who opened the access file?

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Ppeterso
    How does Access capture the NT username? What is the advantage of Access providing the user name verses SQL Server? btw..thanks for the above answer.
    15 pointsBadges:
    report
  • Randym
    If you use linked tables, the logon would be the same for all users of the Access application unless you relink the tables for each Access user. The same goes if you use DAO in VB code. The NT user user is specific to who ever is logged on at that PC running the application at that time. This code works in Access 2003 to get the OS user. You can also use Access security which can be separate from the OS security. Option Compare Database Option Explicit Private Declare Function GetUserNameA Lib "advapi32.dll" (ByVal lpBuffer As String, nSize As Long) As Long Public Function UserLoggedOn() As String Dim cn As String Dim ls As Long Dim res As Long cn = String(1024, 0) ls = 1024 res = GetUserNameA(cn, ls) If res <> 0 Then UserLoggedOn = Mid(cn, 1, InStr(cn, Chr(0)) - 1) Else UserLoggedOn = "" End If End Function
    1,740 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following