Tracking of changes

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" wrote:
> SELECT * FROM sys.procedures ORDER BY modify_date DESC
>
> "simon" wrote in message
>
>
>
>
>
> > 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¾i citirano besedilo -
 >> Stay informed about: Tracking of changes 

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 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" wrote:
>
>
>
> > SELECT * FROM sys.procedures ORDER BY modify_date DESC
>
> > "simon" wrote in message
>
> >
>
> > > 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 -
 >> Stay informed about: Tracking of changes 

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" wrote in message

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 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" wrote:
>
>
>
> > SELECT * FROM sys.procedures ORDER BY modify_date DESC
>
> > "simon" wrote in message
>
> >
>
> > > 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 -
 >> Stay informed about: Tracking of changes 

On 25 jun., 07:20, "Uri Dimant" 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" wrote in message
>
>
> 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 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" wrote:
>
> > > SELECT * FROM sys.procedures ORDER BY modify_date DESC
>
> > > "simon" wrote in message
>
> > >
>
> > > > 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
 >> Stay informed about: Tracking of changes 

simon ( ) 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 RemoveThis @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
 >> Stay informed about: Tracking of changes