September 9, 2003 at 11:02 am
Does anyone have a slick way to record the user name of a particular user connected to sql server through an Access ADP front end? Currently, no matter who is using the ADP, the user is being recorded as Admin (default user group). Thanks!
September 9, 2003 at 11:17 am
try this
run a stored procedure on SQL that enteres the users Name into a table.
This will return the login identification name from a user's security identification number
UPPER(LTRIM(RTRIM(SUSER_SNAME())))
hope this helps
Will
September 9, 2003 at 3:36 pm
Hi Rachel,
you could also try this method:
Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function GetCurrentUserName()
Dim sBuff As String
Dim lConst As Long
Dim lRet As Long
Dim sName As String
lConst = 199
sBuff = Space$(200)
lRet = GetUserName(sBuff, lConst)
GetCurrentUserName = Trim$(Left$(sBuff, lConst))
End Function
HTH
Regards
Chris
September 10, 2003 at 1:26 am
Hi Rachel,
quote:
Does anyone have a slick way to record the user name of a particular user connected to sql server through an Access ADP front end? Currently, no matter who is using the ADP, the user is being recorded as Admin (default user group).
maybe this one will help you
http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=15054
I think, as long as you haven't set up Access security mechanisms, it's normal behaviour that everyone logs on in Access as Admin.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
September 10, 2003 at 1:53 am
quote:
Does anyone have a slick way to record the user name of a particular user connected to sql server through an Access ADP front end? Currently, no matter who is using the ADP, the user is being recorded as Admin (default user group). Thanks!
Surely if you're using an ADP the user account is whatever you have specified in the connection to SQL. If you're trying to get the username through the Access function CurrentUser() it will return Admin. As Frank says, everybody is Admin in Access unless you specify otherwise. But if you use the SQL SYSTEM_USER method you should get the name of the account connected to SQL. Obviously, if your connection forces all logins as a specific user then this won't help at all!
Try:
SubString(SYSTEM_USER, CharIndex('\', SYSTEM_USER)+ 1, Len(SYSTEM_USER))
This will crudely strip off the domain name and leave you with the username only.
September 10, 2003 at 2:11 am
The GetCurrentusername will only get you the name used to login into Windows and not to the SQL Server unless both are the same. The ADP stores connection string to the SQL Server in a property called BAseconnectionString that is readonly. The best way I have handled this is to have my own login form for connecing to the SQL Server and use my own connectionstring.
This means everyone that logins in through the ADP must have an SQL login account.
Hope this helps.
September 10, 2003 at 3:46 am
I use the following:
ALTER PROCEDURE sp_WhoAmI
AS
/* set nocount on */
--DECLARE @sys_usr char(30)
SELECT System_user as SQLUserName
RETURN
September 10, 2003 at 7:48 am
Thanks everyone for your suggestions! I ran into this suggestion on another website and so far it 'seems' to work - using
Environ("username")
captures the username I was looking for. I'm going to keep all your suggestions in case this doesn't end up panning out.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply