February 25, 2008 at 9:10 am
HI Guys...
Actually we are working as a team on database.. but all of v are using "sa" username and password to logon to sql server.. now i have created a different users and their profiles on the server... what i want to have a little help.. How can i create a database level trigger, which will make a inert a row into a specific table that .. this table/procedure/function/ has bee edit/created/delete by this user and the ip address of the machine on which he's working..
Thanks and looking forward.
-MALIK
February 25, 2008 at 9:22 am
Probably the eaiest thing for you to do is to create a trace on create database. But you can create a trigger using
CREATE TRIGGER ddl_Trig_DBCreate
ON ALL SERVER
FOR CREATE_DATABASE
......
Use the EventData function to get the information you want.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 25, 2008 at 9:42 am
CLR is another viable option. Check out David Ziffer's article -
http://qa.sqlservercentral.com/articles/SS2K5+-+CLR+Integration/creatingagenericaudittriggerwithsql2005clr/2502/[/url]
Tommy
Follow @sqlscribeFebruary 25, 2008 at 9:53 am
Thanks for reply..
it's similar to that.. but the code and the out put is on table level and it's concern about data... but i m looking actuall structure of the database..
who has/when/which database/ which table has modify
Thanks and looking forward.
-MALIK
February 25, 2008 at 9:56 am
Have you looked at the schema changes history report available in SSMS? (right-click the server and/or database, select reports, schema changes history report).
Tommy
Follow @sqlscribeFebruary 25, 2008 at 10:03 am
Kenneth Fisher (2/25/2008)
Probably the eaiest thing for you to do is to create a trace on create database. But you can create a trigger usingCREATE TRIGGER ddl_Trig_DBCreate
ON ALL SERVER
FOR CREATE_DATABASE
......
Use the EventData function to get the information you want.
Kenneth
You can use this and restrict it to the database level as well. If you go to BOL and lookup DDL triggers the documentation is pretty good. The EventData function returns an XML doc
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply