July 26, 2011 at 6:07 am
trying ot think outside of the box, i think you could create a job that runs sp_who2 and logs the data to a table...that would have less data, of course, but that's another possbility.
CREATE PROCEDURE PR_CAPTURESP_WHO
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'WHORESULTS') AND xtype in (N'U'))
CREATE TABLE WHORESULTS (
[WHORESULTSID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SPID] CHAR (5) NULL,
[INSERTDT] DATETIME NULL DEFAULT(GETDATE()),
[STATUS] VARCHAR(30) NULL,
[LOGIN] VARCHAR(30) NULL,
[HOSTNAME] VARCHAR(30) NULL,
[BLKBY] VARCHAR(30) NULL,
[DBNAME] VARCHAR(30) NULL,
[COMMAND] VARCHAR(30) NULL,
[CPUTIME] INT NULL,
[DISKIO] INT NULL,
[LASTBATCH] VARCHAR(30) NULL,
[PROGRAMNAME] VARCHAR(200) NULL,
[SPIDINT] INT NULL
)
--table exists, insert some data
INSERT INTO WHORESULTS(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT)
EXEC sp_who2
--don't care about spids less than 50 anyway:
DELETE FROM WHORESULTS WHERE SPIDINT < 50
Lowell
July 26, 2011 at 8:48 am
Just going to throw this out there since it may be another approach. It has been long since I have done this and recall how to script entirely for you but I had thought to recall that you can use the service broker to capture events such as logins. You could set a procedure to log data from an event captured such as a login. The Trigger may be a little more resource intensive, but I didn't do metrics on this.
It might be similar to this: http://weblogs.sqlteam.com/mladenp/archive/2008/07/18/Immediate-deadlock-notifications-without-changing-existing-code.aspx --- this example uses the event notifications to track deadlocks though, not logins.
I am pretty sure that the Aduit_Login as the event you want to track.
http://msdn.microsoft.com/en-us/library/ms189453.aspx -- list of events to track.
Further resources for the service broker and events:
http://msdn.microsoft.com/en-us/library/ms171612.aspx
http://msdn.microsoft.com/en-us/library/ms189453.aspx
Sorry, if I had more time would try to get more for you but just throwing that option into the mix if you are struggling with the DDL Trigger. The login event from what I remember can be traced backside as an event where you can log details. Hope it is of some help.
July 27, 2011 at 3:42 am
Thanks Lowell..
You are good to help, and the steps what you have mentioned are correct as max logins are default to MASTER as it happens in all servers and in all org.
The one which you have shared was pretty good and close to my needs.
Thanks much for your time spending and replying for my each and every questions with patience.
Glad..
I am testing that today, I will post an update on this definitely with the output status and information. I was trying to get this done from past 1 week, now I am done with most of the needs. Not sure, how the client responds on this. Will reply back.
Thanks to one and all.
- Win.
Cheers,
- Win.
" Have a great day "
July 27, 2011 at 3:46 am
Hello matt.newman,
Thank you for your time checking this and providng the links. I have done much research in googling the information, nothing clicked to me. Now, Lowell gave some beautiful tips. But still trying to get this cleared, most likely with Proc which can be helpful for one and all here and out...
Thanks.
I am testing one by one to get the client requirement resolved for life time of this server.....:-)
- Win.
Cheers,
- Win.
" Have a great day "
July 28, 2011 at 1:12 am
Nothing clicked to me.. accdate shows wrong information. Any other help please..!
One more query :
Is there anyway to track the logins last time used on a particular database in SQL Server 2000 ?
find / identify when was the SQL Login last used in SQL Server 2000 ?
Cheers,
- Win.
" Have a great day "
July 29, 2011 at 6:27 am
Hello All,
Sorry track this to another way.
- Is there anyway that to track the Logins, created date, accessed date, when it was accessed the particular database.
Is this question makes any sense ?
Can anyone please let me know about this ?
Cheers,
- Win.
" Have a great day "
July 29, 2011 at 6:38 am
- Win. (7/29/2011)
Hello All,Sorry track this to another way.
- Is there anyway that to track the Logins, created date, accessed date, when it was accessed the particular database.
Is this question makes any sense ?
Can anyone please let me know about this ?
Only with a trace.
there is absolutely no other way to track on a per user/login basis whether someone "accessed"(ie read data via a SELECT statement).
you could track INSERT/UPDATE DELETE via triggers on every table, but the trace is easier., since it would track those events as well.
the trackign of sp_who2 that i posted just shows who is online, but not whether or when they touched an object.
Lowell
May 2, 2012 at 1:32 pm
Couldn't you just enable audit and read the logs of successful logins to a table nightly as a dump using exec sp_readerrorlog results. That will get you who logged in where at least.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply