Audit, track database connections / logins to a table ?

Question

Audit, track database connections / logins to a table ?

Viewing 8 posts - 16 through 22 (of 22 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

matt.newman Hall of Fame Points: 3910 More actions · Answer 2

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.

- Win. SSCertifiable Points: 6790 More actions · Answer 3

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 "

- Win. SSCertifiable Points: 6790 More actions · Answer 4

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 "

- Win. SSCertifiable Points: 6790 More actions · Answer 5

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 "

- Win. SSCertifiable Points: 6790 More actions · Answer 6

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 "

Lowell SSC Guru Points: 323482 More actions · Answer 7

- 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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mfuller333 SSC Enthusiast Points: 130 More actions · Answer 8

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.