On Mar 5, 11:51 am, boyl...@gmail.com wrote:
Quote:
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
Does this help?
Function GetSqlUser() As String
Dim qrd As DAO.QueryDef, rst As DAO.Recordset
Set qrd = CurrentDb.CreateQueryDef("")
With qrd
.Connect = CurrentDb.TableDefs(0).Connect
.SQL = "SELECT SYSTEM_USER"
.ReturnsRecords = True
End With
Set rst = qrd.OpenRecordset()
GetSqlUser = rst(0).Value
rst.Close
Set rst = Nothing
Set qrd = Nothing
End Function
