Post reply

Finding the NT user when using SQL Server authentication

  • January 12, 2007 at 3:46 am

    #62157

    Hi,

    I am creating an INSERT,UPDATE trigger for auditing changes to a table in our main application, which connects all sessions using a single SQL Server Authentication account . Wanting to know which network user was making the changes, I declared a VARCHAR variable @strNTUserName and added the following line of code to my trigger.

    SELECT @strNTUserName = nt_username from master.dbo.sysprocesses where spid=@@spid

    I have found that when I perform an update using the application from my Windows XP desktop, @strNTUserName returns a zero length string.

    However, if I perform and update running the application on our Windows 2000 Terminal Services server using remote desktop, the variable correctly returns my NT login name.

    does anyone know why this value is set in one OS environment and not in the other

    David

    If it ain't broke, don't fix it...

  • January 12, 2007 at 9:01 am

    #682949

    it doesn't work that way - a sql login is a sql login, an ad/nt login is what it is.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • January 12, 2007 at 10:35 am

    #682988

    SQL authentication is commonly used for client which CANNOT use Windows authentication - they are totally separate and a client uses SQL OR NT. 

    I suppose developers use SQL logins for application users when they don't want to go through the hassle of having a domain account created for their app.

    NT is recommended and most secure.

Viewing 3 posts - 1 through 3 (of 3 total)

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