InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 12:02:04 GMT20http://www.sqlservercentral.com/Forums/Topic462175-290-1.aspx[quote][b]@fracionero (5/6/2008)[/b][hr]My insert command:INSERT INTO [SRSQL1001\MANAGEMENT].[DBA_AUDIT].[dbo].[ENV_SRV_INS_DB_CHG] ([ENV_SRV_INS_DB_CHG_Server] ,[ENV_SRV_INS_DB_CHG_Name] ,[ENV_SRV_INS_DB_CHG_EventType] ,[ENV_SRV_INS_DB_CHG_PostTime] ,[ENV_SRV_INS_DB_CHG_Login] ,[ENV_SRV_INS_DB_CHG_TSQLCommand] ,[ENV_SRV_INS_DB_CHG_Completed]) values(@ServerName,@DBName,@EventType,@PostTime,@login,@TSQLCommand,@comp)errors setMsg 3984, Level 16, State 1, Line 1Cannot acquire a database lock during a transaction change.Msg 3985, Level 16, State 2, Line 1An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.Msg 3984, Level 16, State 1, Line 1Cannot acquire a database lock during a transaction change.Msg 3985, Level 16, State 1, Line 1An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.I have checked if I delete insert command database is created without errors.Thanks in advance for your helpF.Racionero[/quote]Has anyone figured out a solution to this? My trigger record is being inserted into the other server, but I sill get these error messages.Thanks!JMBThu, 18 Oct 2012 10:35:05 GMTjodi_burchellhttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxcreate trigger ServerAuditTriggeron all server-- Audit server level and database level into dbadata.dbo.ServerAudit-- The database level is also audited into &lt;database&gt;.dbo.DatabaseAudit[b]with execute as ''server_audit''[/b]for ddl_eventsWed, 28 Sep 2011 03:39:52 GMTSean Elliott (UK)http://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxHow are you guys handling when the server trigger kicks off when a user is in the dbcreator role but doesn't have access to insert into the table that houses all the ddl trigger events? Thks!Mon, 26 Sep 2011 16:54:31 GMTLawrence-136504http://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxYou can see an alternative "article" I wrote for this at:http://www.sqlservercentral.com/Forums/Topic947481-391-1.aspxMon, 06 Dec 2010 06:59:15 GMTSean Elliott (UK)http://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxI am looking for this pagehttp://www.sqlservercentral.com/Forums/Topic948331-149-1.aspx What happened to it?Mon, 06 Dec 2010 06:18:37 GMTL Cernigliahttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxLook here for a full solution: http://www.sqlservercentral.com/Forums/Topic948331-149-1.aspxWed, 07 Jul 2010 00:47:10 GMTSean Elliott (UK)http://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxI love this article: http://www.sqlservercentral.com/articles/Auditing/64176/However, I would like to know how do you audit sp_rename?Or to most DBAs this isn't important.Tue, 14 Oct 2008 07:00:30 GMTL Cernigliahttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxRemember, though, that these database triggers will not catch changes from using sp_rename.Fri, 27 Jun 2008 11:22:12 GMTKenneth Youngerhttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspx[quote][b]mstjean (5/5/2008)[/b][hr]I have a user (developer) that tried to alter a view in a db. He does not have rights to the db ("Admin") I write the 2 tables to. He DOES have rights to do what he was doing.Granting him even write rights to the 2 audit tables seems counter intuitive; I thought the db and server triggers should execute in another security context... maybe this is my incorrect assumption. I don't want to turn on cross db ownership chaining-- that would expose me to a different set of risks when I am trying to ratchet those down.[u]His Error:[/u]Msg 916, Level 14, State 1, Procedure trgMonitorChange, Line 33The server principal "the users domain name" is not able to access the database "Admin" under the current security contextAny thoughts/recommendations?[/quote]Perhaps I should have put something in my article regarding my intention for these triggers as I originally put them on production servers that had limited access. The database and server triggers will run in the security context of the individual account that is triggered by the DDL change. Therefore when the trigger goes off and wants to write the data to a separate database, that user requires write permission to the destination table. In your case you have the following options:1. Change the database trigger to point to an auditing table within the local database instead of a separate database and give that local auditing table write permissions to the developer. 2. Give the developer write permissions on the table that is in the auditing database. This may not be effective if you have more than one account making changes to your database.Tue, 06 May 2008 08:05:24 GMTJames Greaveshttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxHi all,Nice article.I'm working in a similar trigger but I'm recording information in a table in a Linked server for consolidation issues, because I have several instances and several server and I need to collect all information in only a database, but when I try to the insert command, I only get a set of errors. I've checked previously the trigger in the local server and runs ok, but the fails appear when I try to execute from a remote server.My insert command:INSERT INTO [SRSQL1001\MANAGEMENT].[DBA_AUDIT].[dbo].[ENV_SRV_INS_DB_CHG] ([ENV_SRV_INS_DB_CHG_Server] ,[ENV_SRV_INS_DB_CHG_Name] ,[ENV_SRV_INS_DB_CHG_EventType] ,[ENV_SRV_INS_DB_CHG_PostTime] ,[ENV_SRV_INS_DB_CHG_Login] ,[ENV_SRV_INS_DB_CHG_TSQLCommand] ,[ENV_SRV_INS_DB_CHG_Completed]) values(@ServerName,@DBName,@EventType,@PostTime,@login,@TSQLCommand,@comp)errors setMsg 3984, Level 16, State 1, Line 1Cannot acquire a database lock during a transaction change.Msg 3985, Level 16, State 2, Line 1An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.Msg 3984, Level 16, State 1, Line 1Cannot acquire a database lock during a transaction change.Msg 3985, Level 16, State 1, Line 1An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.I have checked if I delete insert command database is created without errors.Thanks in advance for your helpF.RacioneroTue, 06 May 2008 07:53:11 GMT@fracionerohttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxI have a user (developer) that tried to alter a view in a db. He does not have rights to the db ("Admin") I write the 2 tables to. He DOES have rights to do what he was doing.Granting him even write rights to the 2 audit tables seems counter intuitive; I thought the db and server triggers should execute in another security context... maybe this is my incorrect assumption. I don't want to turn on cross db ownership chaining-- that would expose me to a different set of risks when I am trying to ratchet those down.[u]His Error:[/u]Msg 916, Level 14, State 1, Procedure trgMonitorChange, Line 33The server principal "the users domain name" is not able to access the database "Admin" under the current security contextAny thoughts/recommendations?Mon, 05 May 2008 18:50:04 GMTmstjeanhttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspx[quote][b]geno wald (4/3/2008)[/b][hr]This appears to be pretty much what I would want, except that I would like to be able to capture the machine name associated with the user who is making the connection. When I view the Activity Monitor in SSMS under the Management node, I can see the client computer name identified as "Host", but it is not clear how I can access that value from the trigger. The reason this would be helpful is that there might be several people who share access to an account. Using the client computer name to distinguish where the login came from would help identify the person making those ddl changes.[/quote]Within the database and/or server trigger you may create another variable to capture the hostname and any other information you would like from the sys.sysprocesses table.e.g. DELCARE @hostnm VARCHAR(50)SELECT @hostnm = hostnameFROM sys.sysprocessesWHERE loginame = suser_sname()and cmd &lt;&gt; 'awaiting command'You would then alter the tables hosting the information to add a column for host name. Finally, you may also want to capture the "net_address" column within the sys.sysprocesses table to get the MAC address of the network card as well as the hostname.Fri, 04 Apr 2008 08:48:06 GMTJames Greaveshttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxThis appears to be pretty much what I would want, except that I would like to be able to capture the machine name associated with the user who is making the connection. When I view the Activity Monitor in SSMS under the Management node, I can see the client computer name identified as "Host", but it is not clear how I can access that value from the trigger. The reason this would be helpful is that there might be several people who share access to an account. Using the client computer name to distinguish where the login came from would help identify the person making those ddl changes.Thu, 03 Apr 2008 19:26:59 GMTgeno waldhttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxThanks very much for your reply!Peter.Mon, 03 Mar 2008 11:02:47 GMTpkennyhttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxThe attached code is not required and was only meta data from the scripted 2005 SQL view.Mon, 03 Mar 2008 08:56:18 GMTJames Greaveshttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspx[quote][b]pkenny (2/29/2008)[/b][hr]What is unique about server 2005? Can't this be done with previous versions of SQL. Nice solution BTW! Presumably the new database is secured in some way eg. you are the DBO?Peter.[/quote]DDL triggers is a 2005 option only. The new database is secured in a way so that only administrators (sa) would have access. Since that is usually not enough, one could put a trigger on tables that hold the information that would have a contraint that would only allow you to delete if you were logged in with your domain name. e.g. IF suser_sname() = 'yourdomain\username'BEGIN EndELSE ROLLBACK TRANENDSomething like that would be fun to catch those trying to hide the audit trail.Mon, 03 Mar 2008 08:53:58 GMTJames Greaveshttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspx"The attached SQL to this article contains the extended meta data for the views and is not shown in the code below."When I scripted out the views for this article from SQL 2005 there was extended meta data with the actual view T-SQL code that I did not include with the original article. This meta data is not required for the views for the code to work.Mon, 03 Mar 2008 08:48:10 GMTJames Greaveshttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxI like what it does and i also created 2 SSRS reports but my question is how do you make this work with users that are not sa and may not have permission to the audit db? that is the only issue i have. I love the out put it gives us and it is very scaleable.JimFri, 29 Feb 2008 13:24:18 GMTJames Reedhttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxHi Jonathan,Sure, there is room for ad-hoc implementation as well. I only wanted to show another option. This one's price really low (500$) does a whole lot more than what this article talks about, and actually frees you to focus on what you do best (your specific project) instead of worrying about issues like version control, which have been solved many times already.But naturally, if you feel your ad-hoc solution answers ALL your needs, there's no need to spend more money!JonathanFri, 29 Feb 2008 11:35:09 GMTyonisionhttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxGood job! Nice to know someone has some of the same problems I do.BTW, you might be interested in looking at this PASS demo. It has some good ideas.The SQL 2005 DDL Audit Samples are a collection of sample applications for configuring DDL (Data Definition Language) auditing on one or more SQL Server 2005 instances. These applications were first presented at the PASS 2006 Community Summit in my presentation Building a DDL Audit Solution using SQL Server 2005. They build on top of the Service Broker and Server Event Notification infrastructure available in SQL Server 2005 to provide a means of collecting all server and database DDL events in a central database for viewing and reporting. Full source code is provided for all sample applications to allow you to better understand the technologies and easily build on or modify these samples for use in your environment.http://www.sqldbatips.com/showarticle.asp?ID=113Fri, 29 Feb 2008 09:25:51 GMTweboothhttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspx[quote][b]yonision (2/29/2008)[/b][hr]There's a nice product that's using this exact technology, but already delivers everything to you with a nice GUI, easily viewable logs and all, and saves you all the hassble (also pushes changes to SourceSafe for you)http://www.nobhillsoft.com/Randolph.aspxthanks[/quote]I see the product you mention and am sure its a worthy tool for deployment where the budget is available. I have taken the code from the article and created the objects concerned in my 'server statistics' database. I have then built two SSRS reports, based on the views created and i now have an easy to access (from anywhere on our WAN) method of seeing whats being changed on my databases. Total work time - maybe an hour in total (if i include the two posts here as well!!) There's a place for quick and dirty solutions beside the paid-for third party tools.thanks again for the article.Fri, 29 Feb 2008 09:06:19 GMTFatherjackhttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxWhat is unique about server 2005? Can't this be done with previous versions of SQL. Nice solution BTW! Presumably the new database is secured in some way eg. you are the DBO?Peter.Fri, 29 Feb 2008 08:47:00 GMTpkennyhttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxThere's a nice product that's using this exact technology, but already delivers everything to you with a nice GUI, easily viewable logs and all, and saves you all the hassble (also pushes changes to SourceSafe for you)http://www.nobhillsoft.com/Randolph.aspxthanksFri, 29 Feb 2008 08:33:29 GMTyonisionhttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxWhere can I download the SQL code? I see it mentioned in the article, but no link to download.Fri, 29 Feb 2008 05:37:14 GMTJason Shttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxHow can this monitor if the DBA can easily either turn it off or delete items from the output table? Seems to me that if you want a solution like this, the DBAs can't be the ones controlling what is recorded.Fri, 29 Feb 2008 02:57:24 GMTawdolanhttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxGreat example of something i have been wanting to implement for a long time.Step 2 mentions 'attached SQL' but i cant see it, what am i doing wrong?Fri, 29 Feb 2008 02:56:40 GMTFatherjackhttp://www.sqlservercentral.com/Forums/Topic462175-290-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Auditing/62126/">Monitoring and Recording DDL changes on SQL 2005 (NARC)</A>[/B]Thu, 28 Feb 2008 22:45:12 GMTJames Greaves