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.
| Author |
Topic |
|
arrr
Starting Member
3 Posts |
 Posted - 2010-09-30 : 09:59:02
|
| Hello all!I need to know what is best way to log all activity including:• Add DB User Event• Add Login to Server Role• Add Member to DB Role• Add Role Event• Add login Event• Add App role change password• Change Audit• Change Database Owner• Database Management• Database Object GDR• Database Object Management• Database Object Take Ownership• Database Principal Impersonation• Database Principal Management• Login• Login Failed• Login GDR Event• Logout• Object Derived Permission Event• Schema Object GDR• Schema Object Management• Schema Object Take Ownership• Server Alter Trace• Server Object GDR• Server Object Management• Server Object Take Ownership• Server Operation• Server Principal Impersonation• Server Principal Management• Server Scope GDR• Server Starts and StopsI know some of them are logged in SQL logs but what about rest? Can you recommend creating trigger or something else? I don't want to kill my server :) |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2010-09-30 : 11:53:51
|
| DDL Triggers are your friend if you are talking SQl Server 2005 or 2008.Take a look at:http://msdn.microsoft.com/en-us/library/ms186406(v=SQL.90).aspxOn how to implement. |
 |
|
|
arrr
Starting Member
3 Posts |
Posted - 2010-09-30 : 11:58:04
|
| Thanks!So it's possible to log all this above? And I won't kill my server performance? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-30 : 12:02:19
|
| A server side trace is the way to do this.http://msdn.microsoft.com/en-us/library/ms190362.aspx |
|
|
|
arrr
Starting Member
3 Posts |
Posted - 2010-09-30 : 12:12:28
|
| But is profiler good option for it? As far as I know it will overload server when I will use it 24x7?I need something simple that can run 24x7. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-30 : 12:28:48
|
| don't use profiler. use sp_trace_create.and yes, even that will add overhead. not nearly as much as running profiler will thoughyour other option is to purchase auditing software, such as lumigent auditDB |
|
|
|
|
|
|
Logging almost everything