Tracking of changes
in [Programming SQL]
|
From: simon on 28 Jun 2010 03:15 On 25 jun., 07:20, "Uri Dimant" <u...(a)iscar.co.il> wrote: > Create a trigger on database level > --Preventing changes to database objects > > CREATE TRIGGER trgNoMonkeying ON DATABASE >   FOR DROP_TABLE, ALTER_TABLE > AS >   DECLARE @Message VARCHAR(255) >   SELECT  @message = 'You are forbiddent to alter or delete the ''' >       + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', >                 'nvarchar(100)') + ''' table' >   RAISERROR ( @Message, 16, 1 ) >   ROLLBACK ; > GO > > "simon" <zupan....(a)gmail.com> wrote in message > > news:0e1456bd-4bad-47fa-b821-b836719f25ab(a)d8g2000yqf.googlegroups.com... > Hi, > > also I would like to know if it's possible to see also which user made > a change to some procedure or function and not just last modified > date. > > Regards,Simon > > On 24 jun., 09:49, simon <zupan....(a)gmail.com> wrote: > > > > > Thank you Uri. > > How can I track each change, is there some tool in SQL or I must write > > some kind of trigger? > > > By the way, congratulate for yesterday match with Algeria :) > > > Regards, Simon > > > On 23 jun., 14:09, "Uri Dimant" <u...(a)iscar.co.il> wrote: > > > > SELECT * FROM sys.procedures ORDER BY modify_date DESC > > > > "simon" <zupan....(a)gmail.com> wrote in message > > > >news:b58f082b-0a5a-476b-9ea2-24b6380f85b2(a)j4g2000yqh.googlegroups.com.... > > > > > Hi, > > > > > is it possible to see in sql2008 enterprise edition, when some > > > > object(stored procedure, function,...) was changed? > > > > Any idea, how to watch tracking of changes on objectts? > > > > > Thank you, > > > > Simon- Skrij navedeno besedilo - > > > > - Prika 3/4 i citirano besedilo -- Skrij navedeno besedilo - > > > - Prika¾i citirano besedilo -- Skrij navedeno besedilo - > > - Prikaži citirano besedilo - Hi, what about for alter stored procedure(not just table)? And also to insert somewhere which user is altering the procedure? Thanks, Simon
From: Erland Sommarskog on 28 Jun 2010 17:55
simon (zupan.net(a)gmail.com) writes: > what about for alter stored procedure(not just table)? And also to > insert somewhere which user is altering the procedure? If you want log other events, you would add that to the action list for the trigger. Look up DDL triggers in Books Online for more information. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |