Post reply

nt_username and nt_domain columns of sysprocesses

  • October 12, 2004 at 10:41 am

    #161652

    SQL 7.0

    I have triggers on some tables that write to an activity log whenever data is changed. This trigger references the 'sysprocesses' table to get at the 'nt_username' and 'nt_domain' columns.

    Increasingly, these columns are not being populated but there seems to be no logic as to why.

    All users use SQL Server authentication, from W2K. Some users are on NT4 domain, some are on W2K domain. However, this doesn't seem to correlate with whether or not these fields get populated.

    According to BOL, these fields are only populated when using windows authentication or a trusted connection. This is most definitely not correct.

  • October 12, 2004 at 11:31 am

    #526377

    Kevin,

    you can try using suser_sname or suser_name for SQL 7.0 to capture the user who is running that session.

  • October 12, 2004 at 11:40 am

    #526378

    Those fields are only populated if the user is logging on with a Windows Account (Trusted account). If a SQL Server account (SQL Server authentication) is being used, those fields will be empty.

    -SQLBill

  • October 13, 2004 at 12:44 am

    #526461

    Not sure about SQL 7 , but in 2000 the sysprocesses.loginame column will hold the sql / windows login name used to connect.

    In the cases where a SQL Authentication connection has been made, the nt_username and nt_domain columns will be empty, whilst loginame will be populated. This will be the case when SA or the SQL Service (when not using a windows account) connects.

    Julian Kuiters
    juliankuiters.id.au

  • October 13, 2004 at 3:05 am

    #526482

    Thanks for you replies but I am, unfortunately, still no nearer finding a solution. SUSER_NAME() and SUSER_SNAME() both return the login user, not the NT user name.

    Also, it is not true to say that the nt_username and nt_domain columns will be empty when SQL Authentication connection has been made. They are usually populated. Until recently, they were always populated on our system, now the population is intermittent. Does anyone know why?

  • October 13, 2004 at 3:13 am

    #526483

    I have the answer!

    If the connection is made through TCP/IP, the nt_username and nt_domain columns are empty.

    If the connection is made through named pipes, the columns are populated.

    Thanks to everyone who tried to help.

    (Maybe this would make a good question of the day?)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply