Capture Windows UserName in a Trigger - SQL Server 2000 (Mixed Mode)

In order to test these functions to know what I mean, execute the following statements in Query Analyzer and look at the results:

Select USER
Select SYSTEM_USER

>>Is there a function for it? If not, can you write a custom function to capture this info? <<
If you are logging on as a generic user (like you would in a web application) than no, there is no built in way to get the user name (as it could be anonymous).  There are ways to work around that, but you would have to code things differently in your app.

Try using suser_sname() Function. Try this in query analyzer

PRINT suser_sname()

this will give you the logged in user.

Hi mmbjames,

This user-defined function should do the job. You might have to increase the max value of @loops depending on your server settings and load.
As anthony suggested, there are many other ways to do it.

I choosed to show this one, rather than shorter code using master..xp_cmdshell, because
- xp_cmdshell can be used in a procedure
- a user-defined function is easier to use 'inline' (no need to call a proc)
- most dbas won't grant rights to xp_cmdshell

Here's the code, I hope it will fit your requirements

-- the user-defined function

drop function dbo.udf_get_NT_User
go
create function dbo.udf_get_NT_User() returns varchar(50) as
begin
declare @ret int, @objShell int, @objExec int, @objStdOut int,
      @strCmd varchar(200), @strCmdOutput varchar(2000),
      @wait int, @loops int, @EOF int
declare @outputValue varchar(50)
declare @tempTable table(outputLine varchar(2000))

set @wait=0
set @loops=0
EXECUTE @ret = sp_OACreate 'WScript.Shell', @objShell OUT
if @ret = 0
begin
      set @strCmd = 'nbtstat -a ' + host_name()
      EXECUTE @ret = sp_OAMethod @objShell, 'Exec', @objExec OUT, @strCmd
      if @ret= 0
      begin
            EXECUTE @ret = sp_OAGetProperty @objExec, 'Status', @wait OUT
            while @wait <> 1 and @loops < 500
            begin
                  EXECUTE @ret = sp_OAGetProperty @ObjExec, 'Status', @wait OUT
                  set @loops = @loops + 1
            end
            if @wait=1
            begin
                  EXECUTE @ret = sp_OAMethod @ObjExec, 'StdOut', @objStdOut OUT
                  if @ret=0
                  begin
                        EXECUTE @ret = sp_OAMethod @objStdOut, 'AtEndOfStream', @EOF out
                        while @EOF=0
                        begin
                              EXECUTE @ret = sp_OAMethod @objStdOut, 'ReadLine', @strCmdOutPut out      
                              if @ret=0 insert into @tempTable values(@strCmdOutPut)
                              EXECUTE @ret = sp_OAMethod @objStdOut, 'AtEndOfStream', @EOF out
                        end
                        exec @ret = sp_OADestroy @ObjStdOut
                  end
            end
            exec @ret = sp_OADestroy @ObjExec      
      end
      exec @ret = sp_OADestroy @ObjShell      
end
select @outputvalue = coalesce(@outputvalue, ltrim(rtrim(substring(outputline,1,charindex('<', outputline)-1))))
from @tempTable
where outputLine like '%<03>%' and outputline not like '%' + host_name() + '%'
return @outputvalue
end
go


-- how to use it
-- NOTE : don't omit the owner (dbo) prefix, it's mandatory to call a user-defined function
select dbo.udf_get_NT_User()
-- or 'inline'
insert into my_log_table(logdate, machine, user, NTuser, ....)  values (getdate(), host_name(), suser_sname(), dbo.dbo.udf_get_NT_User(), ...)


Regards
Hilaire

I meant xp_cmdshell can be used ONLY in a stored procedure.
It's not available in user-defined fuctions

Hilaire,

Welcome back!  Long time no see.

Anthony

OK - here is the scenario.
I'm logged into the server as mmbjames (windows login). I log into Query Analyzer using sql login 'sa'. BTW, I'm using SQL2000 (mixed mode)

suser_sname(), user, system_user all returns 'sa'. What I'm trying to capture is my windows login mmbjames.

Hilaire, I'll try your user defined function later today.

A friend suggested that you might be able to modify app_name() to capture both appname and windows user. Any idea how to do this?

app name is specified in the connect string, so if you modify client code you could add the user.

Hilaire - I created your function, but it always returns NULL. What am I doing wrong?

One more point to make. This is not a Web app, rather a Windows app hosted on a citrix server. So technically the user's windows login should be available to capture.

>>A friend suggested that you might be able to modify app_name() to capture both appname and windows user. Any idea how to do this?<<
That is one strategy I was alluding to.  The one I have used in the past is set the WorkstationID in your app and then you will be able to read it back using the T-SQL function HOST().  Caveat:  Some DBA's frown on this, as you are basically masking the real user.

>>Welcome back!  Long time no see.<<
Fortunately my current mission leaves me a little spare time, I should show up again from time to time. Thanks for your greetings, and glad to see you kept up the good work  ^^

>>Hilaire - I created your function, but it always returns NULL. What am I doing wrong?<<
Most likely nothing :/
It works a treat on my test environments, maybe the Citrix environment changes the typical nbtstat command output ...

May I ask you to test the following command, connected as sa, and post the output ?

declare @cmd varchar(200)
set @cmd = 'nbtstat -a ' + host_name()
exec master..xp_cmdshell @cmd

Thanks you
Hilaire

Hilaire - Thanks for you efforts to help with this. Here's the output. I have masked some of the sensitive data. IP, MAC, SERVER and DOMAIN all have correct values..
     
Frontnet Team:
Node IpAddress: [000.000.00.000] Scope Id: []
 
           NetBIOS Remote Machine Name Table
 
       Name               Type         Status
    ---------------------------------------------
    SERVERNAME      <00>  UNIQUE      Registered  
    DOMAINNAME      <00>  GROUP       Registered  
    SERVERNAME      <20>  UNIQUE      Registered  
    DOMAINNAME      <1E>  GROUP       Registered  
 
    MAC Address = 00-00-00-00-00-00
 
   
Backnet:
Node IpAddress: [00.0.0.000] Scope Id: []
 
    Host not found.
NULL

>> I'm logged into the server as mmbjames (windows login). I log into Query Analyzer using sql login 'sa'. BTW, I'm using SQL2000 (mixed mode)

Just butting in here.... if you log in using WIndows authentication rather than SQL authentication to log into SQL Server then all of the prior mentioned functions should work.

This probably requires a change to your application to log in via windows authentication instead of SQL authentication, but it will save you a lot of hassle in working out the windows user, also your users will be most impressed because they don't have to remember YAP (yet another password), and your IT infrastructure people will be most impressed becasue though don't have to create YAY (yet another user) in SQL.