Network Login Info

Question

Post reply

Network Login Info

bobwaldron

Valued Member

Points: 51
More actions
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

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

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

bobwaldron Valued Member Points: 51 More actions · Answer 2

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

george sibbald SSC Guru Points: 104210 More actions · Answer 3

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

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

FP-401493 Right there with Babe Points: 723 More actions · Answer 4

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.

kevin mann Hall of Fame Points: 3894 More actions · Answer 5

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.

bobwaldron Valued Member Points: 51 More actions · Answer 6

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

Sergiy SSC Guru Points: 110208 More actions · Answer 7

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

bobwaldron Valued Member Points: 51 More actions · Answer 8

bobwaldron

Valued Member

Points: 51

May 9, 2007 at 7:35 am

#705201

Good point. Thanks