Retrieve (Get) SQL Username from SQL Server using VBA code and Access 2003

[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={SQ L
Server};Server= servername;Data base=db;Uid=SQL ;Pwd=SQL;"
DB_Connection = strConnection
End Function
Function Current_SQL_Use r() As Variant
Dim strUserName As Variant
Dim rst As New ADODB.Recordset
With rst
.ActiveConnecti on = DB_Connection
.CursorLocation = adUseClient
.Source = "SELECT suser_sname() "
.Open
strUserName = .Fields(0)
.Close
End With
Set rst = Nothing
strUserName = Username
Current_SQL_Use r = strUserName
End Function
Thanks in advance

[Gord]

On Mar 5, 11:51 am, boyl...@gmail.c om wrote:

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={SQ L
Server};Server= servername;Data base=db;Uid=SQL ;Pwd=SQL;"
DB_Connection = strConnection
End Function

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

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

strUserName = Username
Current_SQL_Use r = 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.Creat eQueryDef("")
With qrd
.Connect = CurrentDb.Table Defs(0).Connect
.SQL = "SELECT SYSTEM_USER"
.ReturnsRecords = True
End With

Set rst = qrd.OpenRecords et()
GetSqlUser = rst(0).Value

rst.Close
Set rst = Nothing
Set qrd = Nothing
End Function

[boyleyc]

hi this works perfectly.

How come you have to use DAO and not ADO, seems silly.

thanks a milion, was really starting to bug me :-)

On 5 Mar, 19:32, "Gord" <g...@kingston. netwrote:

On Mar 5, 11:51 am, boyl...@gmail.c om wrote:

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={SQ L
Server};Server= servername;Data base=db;Uid=SQL ;Pwd=SQL;"
DB_Connection = strConnection
End Function

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

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

strUserName = Username
Current_SQL_Use r = 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.Creat eQueryDef("")
With qrd
.Connect = CurrentDb.Table Defs(0).Connect
.SQL = "SELECT SYSTEM_USER"
.ReturnsRecords = True
End With

Set rst = qrd.OpenRecords et()
GetSqlUser = rst(0).Value

rst.Close
Set rst = Nothing
Set qrd = Nothing
End Function- Hide quoted text -

- Show quoted text -