pulling NT domain account in T-SQL (Trigger)
-
Greetings All:
I am attempting to audit activity in database x. Database x is used only by Application x. Unfortunately, Application x uses a generic login, so all activity in the database appears to have been carried out by sysdba. I can grab the machine-name the user is using, so it strikes me as likely that I could also grab their domain credentials, but so far I'm coming up empty.
I've gone through BOL and tried the functions I could find there, but none of them are giving me what I want. I guess I'm looking for the same information that shows up in SQL Profiler under "NTUser".
Does anybody know how to capture the information I'm looking for?
Thanks everybody;
Duncan
-
You are going to be able to grab it only if it uses integrated security or if the app grabs it form client and logs it somewhere on the server
HTH
* Noel -
But if the info I want is available in Profiler, shouldn't it be accessible SOMEHOW????
TIA;
Duncan
-
Try this (Created as a view)
SELECT hostname, program_name, nt_username, net_address, net_library, loginame, nt_domain
FROM master.dbo.sysprocesses
You'd be most interested in hostname I fugure....
DB
The systems fine with no users loggged in. Can we keep it that way ?br> -
Idealy your application should be using Integrated Security; SQL Server should be using Windows Authentication; Permissions should be granted to a Windows Group; and then you'd be able to track your users better with built in functions like USER_NAME, CURRENT_USER, SYSTEM_USER, SESSION_USER.
Julian Kuiters
juliankuiters.id.au -
As earlier pointed out by DB,
SELECT spid ,status ,sid ,hostname ,program_name ,cmd ,cpu ,physical_io ,blocked
,dbid ,convert(sysname, rtrim(loginame)) as loginname ,spid as 'spid_sort', substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char' from
master.dbo.sysprocesses
The loginname column would give you the logged in user name, be it NT user or SQL. I guess this is what you are looking for.
Cheers!
Arvind -
Just to make the point clear sysproceses will give you a snapshot of tha activity at the time you run the query against it (as profiler does) but if you want to log the activity performed by a user then as pointed above use the suser_sname metadata function that works with integrated security.
I have not tested this but if you really want to try it you could perform:
select hostname, loginname from master.dbo.sysprocesses where spid =@@spid
from the trigger and see if you get what you need. Again this is NOT a recommended way but if you are not planning to rewrite the app or change anything on the front end you may give it a shot.
HTH
* Noel -
I have tried all the above and none of them work for me.
------------------------------
The Users are always right - when I'm not wrong! -
Hostname I've got -- but your nt_username works just as well, especially given that I can now join on hostname. Where do I send the cheque?!
-
Actually I think it is strange that the Application X leaves sysdba, and not the NT credentials, which obviously is available.
Where do You see "sysdba" ?
If Application X was using one of the following ( which work with Win Auth )
select suser_sname()
select system_user
Then You might not have this problem ?!
/rockmoose
You must unlearn what You have learnt -
Oh,
And check the defaults on the database columns.
Maybe they are set to insert the database USER, which is not the same as the LOGIN !
/rockmoose
You must unlearn what You have learnt
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply