By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,932 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Mar 5 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Mar 5, 11:51 am, boyl...@gmail.com 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={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

Mar 5 '07 #2

P: n/a
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.com 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={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- Hide quoted text -

- Show quoted text -

Mar 6 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.