February 14, 2003 at 12:19 pm
I want the following details
select SPID=SUBSTRING(CONVERT(varchar(30),spid),1,3),
LOGIN=SUBSTRING(loginame,1,10),
HOSTNAME=SUBSTRING(hostname,1,11),
[DATABASE]=SUBSTRING(db_name(dbid),1,10),
COMMAND=SUBSTRING(cmd,1,16), PROGRAM=SUBSTRING(program_name,1,46),
NT_USER=SUBSTRING(nt_username,1,8)
from master.dbo.sysprocesses where loginame ='sa' and spid >6
whenever the where condition is true. I'm thinking a trigger in sysprocess, dump to another table and select off of that daily, but I'm fishing for an alternative. Any thoughts, idea ?
John Zacharkan
John Zacharkan
February 14, 2003 at 1:04 pm
Did you try to create an index on a system table?
February 14, 2003 at 1:18 pm
A trigger on a system table won't be supported by Microsoft. However, auditing was a big concern, especially to get the NSA's C2 compliance for SQL 2K. Microsoft accomplished this by using the trace system stored procedures (sp_trace_*) and functions (fn_trace_*).
You can audit and filter logins and logouts pretty easily.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply