a sql way to return the current logged in user? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

a sql way to return the current logged in user?

Is there a method to return the current NT User name (or domain user name)?
Host_Name()works for machine name but the requirements is to return the user name. as always thanks! it’s all good
You can use Profiler or sp_who in QA. Luis Martin
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
thanks Luis and Haywood sp_who gives me all user sessions and spids
user_name() returns dbo although the users enter a userID to login to this particular application, I can’t get to that data because it is stored in a foxpro table. (this system is a foxpro/sql hybrid yikes!)
the developers of this app have not yet created a sql table to store the application userID.
So either I build extra programming and get the UserID from the foxpro table or I use a sql method to get the domain login name. any other suggestions welcome! it’s all good
I don’t think you can do that within the backend. In the front-end, provided that it is running under the user’s Windows login, you could use functions like Environ() in VBA to return session details like the login, the domain, the current computer, etc. etc.
thanks Adriaan, I wish I could pass a host_name() param to sp_who which would return the info for the current user connected to sql from that host and perhaps with a little more digging into the sql system tables I can get the domain user. (the loginname in sp_who returns the application). I’ll keep you all posted if I find a way.
it’s all good
SQL Server can only tell you the domain user account if the connection is a "trusted connection" – i.e. when the login is actually a Windows login. In that case SUSER_SNAME() is the proper function to use,. From what you were describing, the client app uses a single hard-coded SQL Server login for all connections, so there’s no trusted connection. For the HOST_NAME(), it also depends – if you have a client app running on a Citrix server or Microsoft Terminal Server installation, then HOST_NAME() will return the server name, not the user’s workstation. Does the client app use the parameter for the application name on the connect string? If it doesn’t, and you can manipulate the connection string used by the client app, then you could include the domain login as the app name, and retrieve it using APP_NAME() – but I know: that’s not a very realistic solution.
I can vouch for Adriaan’s information on the host_name, as we have about half of our folks using Citrix, and we get the beauty of seeing the citrix server as the host for all of them. And user name also means nothing in our environment as the application running uses a single user account for all access. You might be able to code up an extended stored procedure to pull the user’s NT User somehow since it sounds like the application logs in using SQL Security and not Windows security and SQL won’t have the user’s name. Or you could create an ODBC data source for the FoxPro table, and have SQL Server import data on the fly from that data source so that you can query it in SQL Server. I’m not familiar with FoxPro at all so I’m not sure if it would offer you the same type of thing that Access does "A LINKED table" where Access shows you the table, but the data is really in SQL Server, but an Access application doesn’t know the difference. If that were the case with FoxPro, then you could move the table definition to SQL Server, and have FoxPro just "LINK" to it so that the FoxPro application continues to think it is really in FoxPro. Again I have no idea if that is a possibility or not, like it is with Access.
Just an update: Since FoxPro is ODBC compliant database I could have created a linked server scenario in SQL and had access to the FoxPro tables that store the userID’s.
However I had no way of knowing what userID was the current user using the application. fyi: there was debate in the office to "simply name the workstation the login name of the user" this way HOST_NAME()would work. Various reasons this is a dumb idea: a.bad pratice
b.not flexible since users switch between workstations
c.doesn’t work using terminal server Solution: use VBA On add or modify, the VBA code retrieves the current userID from the FoxPro app session object. I then take this userID and pass it to a field on the form. The user saves the record and my userID is saved in a table. thanks again for everyone’s help it’s all good