DB Login/Logon Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

DB Login/Logon Trigger

Author

Topic

dmaxj
Posting Yak Master

174 Posts

Posted - 2011-02-03 : 15:07:15

I have been able to find docs and write code to handle CREATE, DROP, and ALTER events on tables in a database.

Is there a trigger event fired for users that login to a database? Please advise.

Here is my current code for a drop table event.
Please advise.


USE  RecordCollection
GO
CREATE TRIGGER tr_DropTable
ON DATABASE
FOR  DROP_TABLE
AS
   BEGIN
    PRINT'Drop Table'
   END
GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-03 : 15:08:38

http://technet.microsoft.com/en-us/library/bb326598.aspx

dmaxj
Posting Yak Master

174 Posts

Posted - 2011-02-03 : 15:33:51

russell,

thanks for your reply - I had already run across those docs.
They seem to cover Logon triggers are the server level. I am concentrating on who logs in to a specific database.

Please advise.

Also, I am focused on SQL 2005 -

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-03 : 15:52:56

You can trace for the Audit Database Object Access Event.

Not sure that you can capture it in a trigger though...

dmaxj
Posting Yak Master

174 Posts

Posted - 2011-02-03 : 17:50:38

Thank you, russell.

When you say 'trace' - do you mean that I can trace from Profiler or do a server-side trace on the event or both?

Regards

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-03 : 23:46:55

Both

Think of profiler as a GUI on top of a server side trace. Anything you can do from one, you can do from the other. Profiler introduces some additional overhead however.

dmaxj
Posting Yak Master

174 Posts

Posted - 2011-02-04 : 16:43:15

Thanks, russell. I have explored both options, and the server-side trace seems to be what I am looking for.

However, I have found that I am not so much interested in auditing logins, but I am more interested in knowing who access a particular column in a table.

I am looking at trace events here - http://msdn.microsoft.com/en-us/library/ms186265%28v=SQL.90%29.aspx

But, I do not see an event that covers a SELECT, or DELETE, etc on a table, let alone a column.

Maybe I should start a new thread.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-04 : 17:02:51

New thread here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=156192

Locking this one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources