none
how to get windows user name in a stored procedure?

    Question

  • I have a client server windows system (NTIER). The client application creates a WCF object provided by the windows service. This WCF object has methods which reads and writes in a MS SQL server 2008 database using tableadapters.  These tableadapters uses strored procedures to select, update, delete and insert. In these stored procedure I need to get the windows client user name, but I only get the windows service user name (ex. NT AUTHORITY\SYSTEM). I tried using SYSTEM_USER but it returns the service user name.

    Can someone help me?


    MCP
    Friday, April 02, 2010 2:34 PM

Answers

  • That appears to be a 3-tier client-server application.

    SQL Server does not "see" the real user only the "middleman". You need to handle user identification in the client software in such a case.  For example, you can maintain a login table in the database as a mean of identification.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Friday, April 02, 2010 2:58 PM

All replies

  • That appears to be a 3-tier client-server application.

    SQL Server does not "see" the real user only the "middleman". You need to handle user identification in the client software in such a case.  For example, you can maintain a login table in the database as a mean of identification.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Friday, April 02, 2010 2:58 PM
  • Hello,

    Windows Services run before and while someone is logged into the machine.

    The service is executing the stored procedure, hence, there is no user per se.

    One option is to use impersonation that would prompt the user to start/stop the service, in effect, including the user. However, as previously mentioned, a service doesn't require login to run.

    Adam


    Dibble and dabble but please don't babble.
    Friday, April 02, 2010 3:08 PM
  • BTW, running your service as NT AUTHORITY\SYSTEM, or any Administrator account, is undesirable (unless you have throughly considered your needs and the consequences).

     

    Friday, April 02, 2010 3:10 PM
  • BTW, running your service as NT AUTHORITY\SYSTEM, or any Administrator account, is undesirable (unless you have throughly considered your needs and the consequences).

    Friday, April 02, 2010 3:11 PM
  • You got my problem, it's a ntier client server application, but I did not understand how I could distinguish the user in the stored procedure with a login table. Could you explain me in more detail?

    Thanks.


    MCP
    Saturday, April 03, 2010 1:38 PM