Get SQL authentication Name from SQL Server using access front End

[boyleyc]

Hi all

I am trying to retrieve the SQL username for the currently logged on
user from my backend SQL Database so i can populate a form.

I have the following 2 procedures. However obviously its always
returning the username SQL as per the connection string. Is there a
way of doing this for the CURRENTLY LOGGED IN USER?? if i remove
credentials from the connection string the procedure fails. Also if i
use 'CurrentProject.connection' this also fails as it refers to a JET
connection

Public Function DB_Connection() As Variant
'DB_Connection = CurrentProject.Connection
Dim strConnection As String
strConnection = "Driver={SQL
Server};Server=servername;Database=db;Uid=SQL;Pwd=SQL;"
DB_Connection = strConnection
End Function


Function Current_SQL_User() As Variant
Dim strUserName As Variant
Dim rst As New ADODB.Recordset

With rst
.ActiveConnection = DB_Connection
.CursorLocation = adUseClient
.Source = "SELECT suser_sname() "
.Open
strUserName = .Fields(0)
.Close
End With
Set rst = Nothing

strUserName = Username
Current_SQL_User = strUserName

End Function


Thanks in advance