Post reply

Network Login Info

  • May 4, 2007 at 3:15 pm

    #119999

    The application I'm working with  hardcodes a sql user name via an ini file. I am creating triggers on critical table to capture updates. My trigger is working fine, except the auditors want the Microsoft Network User Name stored with the other audit info. Every thing I've found returns good info (server, dbo, even the hardcoded sqlserver login name), but not the Username. Any thoughts???

     

    Bob

  • May 4, 2007 at 4:53 pm

    #704484

    Did you pass this information to SQL Server in any way?

    So where it suppose to get it from?

    Only if you have a crystal ball inside of your server...

    _____________
    Code for TallyGenerator

  • May 7, 2007 at 7:30 am

    #704630

    I know... there in is my problem. Instead of using windows IDs they tie everything to a canned ID. I'm looking into what little user specific information is passed into the system.

    Thanks

  • May 7, 2007 at 9:36 am

    #704683

    table master..sysprocesses is the place to look to see what info you have on each connection. column net_address will give the mac address user connected from so that might be a way to differentiate them

    ---------------------------------------------------------------------

  • May 7, 2007 at 11:08 am

    #704726

    If you need hostname in the sql server ... try host_name() function

    syntax:

    Select @fp = Host_name()

    I hope I helped.

    FP

    True Love is Like A Ghost. Everyone Talks About It & Nobody have seen it.

  • May 7, 2007 at 4:42 pm

    #704792

    2 ways to acheive this. The first would be to create a x-ref table of sql-user to NT user. When you run the INSERT/UPDATE/DELETE trigger, you would just fire a functions that basically says SELECT NT_USER FROM USER_XREF WHERE SQL_USER=SYSTEM_USER. You would need two columns for this update_user_name and xref_user_name. 2nd way would be to just get rid of the ini files and use NT Auth. Ini files are dangerous security risks. Just apply Murphey's Law.

    ..my bad.. I beleive you can use an extended stored procedure, but I get the funny feeling it will pull the login off of the Server instead of the client.

  • May 8, 2007 at 10:28 am

    #704997

    select nt_domain,nt_username,loginame,hostname from master..sysprocesses where spid=@@spid

    This is what I ended up with. Thanks to all for your input.

     

    Bob

  • May 8, 2007 at 4:30 pm

    #705102

    Don't include queries to system tables into procedures called by regular users.

    It's not allowed in every environment.

    Check BOL for "system functions", "security functions"

    select suser_sname(), CURRENT_USER, system_user, host_name(), etc.

    will give you information you need.

    _____________
    Code for TallyGenerator

  • May 9, 2007 at 7:35 am

    #705201

    Good point. Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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