|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
CREATE TRIGGER on system tablesI need to be notified of each sp that is beeing added or altered.
I would like to create a trigger on sysobjects for this pupose. When I run the CREATE TRIGGE the following error msg received: CREATE TRIGGER permission denied on object 'sysobjects', database 'ABC', owner 'dbo'. You can do it only if you are using SQL Server 2005.
Show quote "Yaniv" <yan***@rediffmail.com> wrote in message news:%23P3XMT1FGHA.752@TK2MSFTNGP12.phx.gbl... >I need to be notified of each sp that is beeing added or altered. > > I would like to create a trigger on sysobjects for this pupose. > When I run the CREATE TRIGGE the following error msg received: > CREATE TRIGGER permission denied on object 'sysobjects', database 'ABC', > owner 'dbo'. > > > No. You cannot create trigger on system tables.
Show quote "Yaniv" <yan***@rediffmail.com> wrote in message news:%23P3XMT1FGHA.752@TK2MSFTNGP12.phx.gbl... >I need to be notified of each sp that is beeing added or altered. > > I would like to create a trigger on sysobjects for this pupose. > When I run the CREATE TRIGGE the following error msg received: > CREATE TRIGGER permission denied on object 'sysobjects', database 'ABC', > owner 'dbo'. > > > Thats NOT recommendable and should be avoided. Imagine if you messed up
the trigger code and the statement fired from the server engine is cancelled, that would mess up and perhaps the system. See the remarks on the BOL: "Note Because Microsoft does not support the addition of user-defined triggers on system tables, it is recommended that no user-defined triggers be created on system tables." If you are using SQL2k5, you can take the advantage of DDL triggers. Otherwise for SQL2k you should use some other auditing, like exploring the transaction log with some logexplorer. HTH, jens Suessmeyer. I understand what you have mentioned, however, how can I achive it?
Show quote "Jens" <J***@sqlserver2005.de> wrote in message news:1137056609.524264.246240@g43g2000cwa.googlegroups.com... > Thats NOT recommendable and should be avoided. Imagine if you messed up > the trigger code and the statement fired from the server engine is > cancelled, that would mess up and perhaps the system. See the remarks > on the BOL: > > "Note Because Microsoft does not support the addition of user-defined > triggers on system tables, it is recommended that no user-defined > triggers be created on system tables." > > If you are using SQL2k5, you can take the advantage of DDL triggers. > Otherwise for SQL2k you should use some other auditing, like exploring > the transaction log with some logexplorer. > > HTH, jens Suessmeyer. > You can't, which is what the error message you posted tell you. If you tell us what it is you want
to achieve, perhaps someone here can offer suggestions. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Yaniv" <yan***@rediffmail.com> wrote in message news:eWzJmz1FGHA.3900@TK2MSFTNGP10.phx.gbl... >I understand what you have mentioned, however, how can I achive it? > > > "Jens" <J***@sqlserver2005.de> wrote in message > news:1137056609.524264.246240@g43g2000cwa.googlegroups.com... >> Thats NOT recommendable and should be avoided. Imagine if you messed up >> the trigger code and the statement fired from the server engine is >> cancelled, that would mess up and perhaps the system. See the remarks >> on the BOL: >> >> "Note Because Microsoft does not support the addition of user-defined >> triggers on system tables, it is recommended that no user-defined >> triggers be created on system tables." >> >> If you are using SQL2k5, you can take the advantage of DDL triggers. >> Otherwise for SQL2k you should use some other auditing, like exploring >> the transaction log with some logexplorer. >> >> HTH, jens Suessmeyer. >> > > And additionally post us the version of SQL Server you are using.
-Jens Suessmeyer. version 2000 sp3a
What I would like to achieve is a situation where in my development enviroment I allow some developers to create and alter procs but I want to be notified of such an activity Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:Oa2F831FGHA.3976@TK2MSFTNGP11.phx.gbl... > You can't, which is what the error message you posted tell you. If you > tell us what it is you want to achieve, perhaps someone here can offer > suggestions. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Yaniv" <yan***@rediffmail.com> wrote in message > news:eWzJmz1FGHA.3900@TK2MSFTNGP10.phx.gbl... >>I understand what you have mentioned, however, how can I achive it? >> >> >> "Jens" <J***@sqlserver2005.de> wrote in message >> news:1137056609.524264.246240@g43g2000cwa.googlegroups.com... >>> Thats NOT recommendable and should be avoided. Imagine if you messed up >>> the trigger code and the statement fired from the server engine is >>> cancelled, that would mess up and perhaps the system. See the remarks >>> on the BOL: >>> >>> "Note Because Microsoft does not support the addition of user-defined >>> triggers on system tables, it is recommended that no user-defined >>> triggers be created on system tables." >>> >>> If you are using SQL2k5, you can take the advantage of DDL triggers. >>> Otherwise for SQL2k you should use some other auditing, like exploring >>> the transaction log with some logexplorer. >>> >>> HTH, jens Suessmeyer. >>> >> >> > Yaniv ,shalom
Look , if your developers have their own copies of the database and make some changes ,so there is a third part tools that can compare sources on poroduction and on development sites. SQL Server 2000 DOES NOT support this option , however you may want to write your own script to compare code of SP doe instance Show quote "Yaniv" <yan***@rediffmail.com> wrote in message news:ekItqD2FGHA.4036@TK2MSFTNGP12.phx.gbl... > version 2000 sp3a > > What I would like to achieve is a situation where in my development > enviroment I allow some developers to create and alter procs but I want to > be notified of such an activity > > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote > in message news:Oa2F831FGHA.3976@TK2MSFTNGP11.phx.gbl... >> You can't, which is what the error message you posted tell you. If you >> tell us what it is you want to achieve, perhaps someone here can offer >> suggestions. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "Yaniv" <yan***@rediffmail.com> wrote in message >> news:eWzJmz1FGHA.3900@TK2MSFTNGP10.phx.gbl... >>>I understand what you have mentioned, however, how can I achive it? >>> >>> >>> "Jens" <J***@sqlserver2005.de> wrote in message >>> news:1137056609.524264.246240@g43g2000cwa.googlegroups.com... >>>> Thats NOT recommendable and should be avoided. Imagine if you messed up >>>> the trigger code and the statement fired from the server engine is >>>> cancelled, that would mess up and perhaps the system. See the remarks >>>> on the BOL: >>>> >>>> "Note Because Microsoft does not support the addition of user-defined >>>> triggers on system tables, it is recommended that no user-defined >>>> triggers be created on system tables." >>>> >>>> If you are using SQL2k5, you can take the advantage of DDL triggers. >>>> Otherwise for SQL2k you should use some other auditing, like exploring >>>> the transaction log with some logexplorer. >>>> >>>> HTH, jens Suessmeyer. >>>> >>> >>> >> > > What I actually need is to have the developers write and alter procs on a
dev srv but I just need to be notified of each proc being added /altered. If I was able to catch the DDL on sysobjects where type = 'u' I think it whould have done the job for me. I am not talking about comparing databases data schema or procs etc. Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:u4PhGR2FGHA.2036@TK2MSFTNGP14.phx.gbl... > Yaniv ,shalom > > Look , if your developers have their own copies of the database and make > some changes ,so there is a third part tools that can compare sources on > poroduction and on development sites. SQL Server 2000 DOES NOT support > this option , however you may want to write your own script to compare > code of SP doe instance > > > > > "Yaniv" <yan***@rediffmail.com> wrote in message > news:ekItqD2FGHA.4036@TK2MSFTNGP12.phx.gbl... >> version 2000 sp3a >> >> What I would like to achieve is a situation where in my development >> enviroment I allow some developers to create and alter procs but I want >> to be notified of such an activity >> >> >> >> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote >> in message news:Oa2F831FGHA.3976@TK2MSFTNGP11.phx.gbl... >>> You can't, which is what the error message you posted tell you. If you >>> tell us what it is you want to achieve, perhaps someone here can offer >>> suggestions. >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://www.solidqualitylearning.com/ >>> Blog: http://solidqualitylearning.com/blogs/tibor/ >>> >>> >>> "Yaniv" <yan***@rediffmail.com> wrote in message >>> news:eWzJmz1FGHA.3900@TK2MSFTNGP10.phx.gbl... >>>>I understand what you have mentioned, however, how can I achive it? >>>> >>>> >>>> "Jens" <J***@sqlserver2005.de> wrote in message >>>> news:1137056609.524264.246240@g43g2000cwa.googlegroups.com... >>>>> Thats NOT recommendable and should be avoided. Imagine if you messed >>>>> up >>>>> the trigger code and the statement fired from the server engine is >>>>> cancelled, that would mess up and perhaps the system. See the remarks >>>>> on the BOL: >>>>> >>>>> "Note Because Microsoft does not support the addition of user-defined >>>>> triggers on system tables, it is recommended that no user-defined >>>>> triggers be created on system tables." >>>>> >>>>> If you are using SQL2k5, you can take the advantage of DDL triggers. >>>>> Otherwise for SQL2k you should use some other auditing, like exploring >>>>> the transaction log with some logexplorer. >>>>> >>>>> HTH, jens Suessmeyer. >>>>> >>>> >>>> >>> >> >> > > SQL Profiler can be of help here. Look at this example of a "select trigger"
by Dejan Sarka and maybe you get an idea on how to implement a DDL trigger in SQL 2000: http://solidqualitylearning.com/blogs/dejan/archive/2004/11/25/214.aspx ML --- http://milambda.blogspot.com/ Yaniv,
I would also take a look at SQL Compare by Red Gate... Fairly inexpensive and many of us use it to compare database structures, even data.. -- Show quoteWayne Snyder MCDBA, SQL Server MVP Mariner, Charlotte, NC I support the Professional Association for SQL Server ( PASS) and it''s community of SQL Professionals. "Yaniv" wrote: > I need to be notified of each sp that is beeing added or altered. > > I would like to create a trigger on sysobjects for this pupose. > When I run the CREATE TRIGGE the following error msg received: > CREATE TRIGGER permission denied on object 'sysobjects', database 'ABC', > owner 'dbo'. > > > >  |
|||||||||||||||||||||||
Other interesting topics