SQL Server Auditing on DDL Activities

Question

Hi

We are trying to audit the DDL activities performed by sysadmins at both server and database level using the below two audit action types in sql server 2008.

DATABASE_OBJECT_CHANGE_GROUP
SCHEMA_OBJECT_CHANGE_GROUP

When I select these two audit action types it does not show the 'Object Class', 'Object Schema', 'Object Name' and 'Principal name' to set fields.

Is there any other alternate way to track the DDL activities for limited users?

Thank you

 

---

Regards; Sureddy; Database Administrator

Answer 1

You Can Trace Above Audit on all users and Filter them using following Query

select * from fn_get_audit_file('C:\YourAuditfile.sqlaudit',default,default)

---

Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com

Answer 2

Kuldeep Bisht

Here the requirement is auditing should record DDL activities performing by only Sysadmins. We dont want to record DDL activities performed by all the users.

 

Thank you

---

Regards; Sureddy; Database Administrator

Answer 3

Yes, see if this helps, it's really fully tested, follow instructions and let me know what you think, apply before in test.

IF YOU APPLY IN PRODUCTION BE EXTREMELY CAREFULL TO FOLLOW INSTRUCTIONS, otherwise you could block DDL operations.

Regards,

           Marco

 

 
 
 
--create Utils database
 
 
--Table to log all the commands raised, not just the one for release,

USE [Utils]
GO

CREATE TABLE [dbo].[ChangeLogging_DDLCommands](                           
 [Id] [int] identity (1,1) NOT NULL,
 [date] [datetime] NULL,
 [user] [varchar](200) NULL,
 [servername] [varchar](200) NULL,
 [db] [varchar](200) NULL,
 [event] [varchar](max) NULL,
 [application] [varchar](200) NULL,
 [hostname] [varchar](200) NULL,
 [EventType] [varchar](max) NULL,
 [SchemaName] [varchar](max) NULL,
 [ObjectName] [varchar](max) NULL,
 [ObjectType] [varchar](max) NULL,
 [OriginalUser] [varchar](200) NULL
) ON [PRIMARY]

GO

 ----------------------------------------------------------------------------------

 

--stand by procedure call by the trigger (this serves to associate automatically to a new login the grant to write into Utils table
--otherwise the ddl operation will fail)

USE [Utils]
GO

CREATE PROCEDURE [dbo].[ChangeLogging_GrantUserToWrite]
 @LoginName varchar(100)
AS 
 EXEC ('IF NOT EXISTS (SELECT 1 FROM sys.sysusers WHERE [name] = ''' +
    @LoginName + ''') CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + ']')

 GO

 ----------------------------------------------------------------------------------

 

--To create a trigger raised at a creation of a new login,
-- this will grant the login to "see" the Utils db, in whose public profile
-- it will find grants to write on the ChangeLogging_DDLCommands table
CREATE TRIGGER [ChangeLogging_CallGrantUserToWrite]
ON ALL SERVER
FOR 
 CREATE_LOGIN,
 ALTER_LOGIN
AS

 SET ARITHABORT ON

 SET CONCAT_NULL_YIELDS_NULL ON

 SET QUOTED_IDENTIFIER ON

 SET ANSI_NULLS ON

 SET ANSI_PADDING ON

 SET ANSI_WARNINGS ON

 SET NUMERIC_ROUNDABORT OFF

 DECLARE @LoginName VARCHAR(100)

 SET @LoginName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'))

 IF @LoginName <> 'sa'
  EXEC Utils.dbo.ChangeLogging_GrantUserToWrite @LoginName
GO

ENABLE TRIGGER [ChangeLogging_CallGrantUserToWrite] ON ALL SERVER
GO

  ----------------------------------------------------------------------------------

 

 

-- this to create a new procedure automatically in a new created db, do not create if not needed
USE [model]
GO

CREATE PROCEDURE [dbo].[ChangeLogging_DropTrigger]
AS
DROP TRIGGER [ChangeLogging_LogDDLCommands] ON DATABASE

GO

 ----------------------------------------------------------------------------------

-- this to delete automatically the proc into the tempdb, that creates when slq start and because of model will have the proc.
-- DO NOT CREATE IF NOT IN MODELDB

USE master
go

CREATE PROCEDURE [dbo].[ChangeLogging_CheckTempDbTrigger]
AS
IF  EXISTS (SELECT * FROM tempdb.sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'ChangeLogging_LogDDLCommands')
EXEC tempdb.dbo.[ChangeLogging_DropTrigger]
go

sp_procoption @ProcName = 'ChangeLogging_CheckTempDbTrigger',
              @OptionName = 'startup',
              @OptionValue = 'ON'

GO

  ----------------------------------------------------------------------------------

 

-- to give grants to public role into Utils

USE [Utils]
GO
GRANT INSERT ON [dbo].[ChangeLogging_DDLCommands] TO [public]
GO

 

use [Utils]
GO
GRANT EXECUTE ON [dbo].[ChangeLogging_GrantUserToWrite] TO [public]
GO

  ----------------------------------------------------------------------------------

 

 

 

-- execute the script that you create with this select on Utils db
-- clean it from nonsense users for this action like

-- [sa]
-- [##MS_SQLResourceSigningCertificate##]
-- [##MS_SQLReplicationSigningCertificate##]
-- [##MS_SQLAuthenticatorCertificate##]

select 'CREATE USER [' + Name + '] FOR LOGIN [' + LoginName + ']' from sys.syslogins

 

  ----------------------------------------------------------------------------------

 

-- EXECUTE ONE PER DB YOU WANT TO TRAP

--DB level trigger, FOR sql 2005, AHEAD THE ONE FOR 2008
--(the change stands on the list of events trapped)

CREATE TRIGGER [ChangeLogging_LogDDLCommands]                            
ON DATABASE
FOR 
CREATE_APPLICATION_ROLE,
 ALTER_APPLICATION_ROLE,
 DROP_APPLICATION_ROLE,
 
CREATE_ASSEMBLY,
 ALTER_ASSEMBLY,
 DROP_ASSEMBLY,
 
ALTER_AUTHORIZATION_DATABASE,
  
CREATE_CERTIFICATE,
 ALTER_CERTIFICATE,
  DROP_CERTIFICATE,
 
CREATE_CONTRACT,
 DROP_CONTRACT,
 
GRANT_DATABASE,
 DENY_DATABASE,
 REVOKE_DATABASE,
 
CREATE_EVENT_NOTIFICATION,
 DROP_EVENT_NOTIFICATION,
 
CREATE_FUNCTION,
 ALTER_FUNCTION,
 DROP_FUNCTION,
 
CREATE_INDEX,
 --ALTER_INDEX,
 DROP_INDEX,
 
CREATE_MESSAGE_TYPE,
 ALTER_MESSAGE_TYPE,
 DROP_MESSAGE_TYPE,
 
CREATE_PARTITION_FUNCTION,
 ALTER_PARTITION_FUNCTION,
 DROP_PARTITION_FUNCTION,
 
CREATE_PARTITION_SCHEME,
 ALTER_PARTITION_SCHEME,
 DROP_PARTITION_SCHEME,
 
CREATE_PROCEDURE,
 ALTER_PROCEDURE,
 DROP_PROCEDURE,
 
CREATE_QUEUE,
 ALTER_QUEUE,
 DROP_QUEUE,
 
CREATE_REMOTE_SERVICE_BINDING,
 ALTER_REMOTE_SERVICE_BINDING,
 DROP_REMOTE_SERVICE_BINDING,
 
CREATE_ROLE,
 ALTER_ROLE,
 DROP_ROLE,
 
CREATE_ROUTE,
 ALTER_ROUTE,
 DROP_ROUTE,
 
CREATE_SCHEMA,
 ALTER_SCHEMA,
 DROP_SCHEMA,
 
CREATE_SERVICE,
 ALTER_SERVICE,
 DROP_SERVICE,
 
CREATE_STATISTICS,
 DROP_STATISTICS,
 --UPDATE_STATISTICS,
 
CREATE_SYNONYM,
 DROP_SYNONYM,
 
CREATE_TABLE,
 ALTER_TABLE,
 DROP_TABLE,
 
CREATE_TRIGGER,
 ALTER_TRIGGER,
 DROP_TRIGGER,
 
CREATE_TYPE,
 DROP_TYPE,
 
CREATE_USER,
 ALTER_USER,
 DROP_USER,
 
CREATE_VIEW,
 ALTER_VIEW,
 DROP_VIEW,
 
 CREATE_XML_SCHEMA_COLLECTION,
  ALTER_XML_SCHEMA_COLLECTION,
 DROP_XML_SCHEMA_COLLECTION

AS                                                      

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET NUMERIC_ROUNDABORT OFF

--
DECLARE @s VARCHAR(MAX)
DECLARE @EventType VARCHAR(MAX)
DECLARE @SchemaName VARCHAR(MAX)
DECLARE @ObjectName VARCHAR(MAX)
DECLARE @ObjectType VARCHAR(MAX)

SET @s = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') 
SET @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)') 
SET @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
SET @ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')

--INSERT INTO Utils.dbo.ChangeLogging_DDLCommands ([date], [user], db, [event], [application], [hostname], [EventType], [SchemaName], [ObjectName], [ObjectType], [OriginalUser])
-- VALUES (getdate(), SYSTEM_USER, DB_NAME(), @s, APP_NAME(), HOST_NAME(), @EventType, @SchemaName, @ObjectName, @ObjectType, ORIGINAL_LOGIN())
INSERT INTO Utils.dbo.ChangeLogging_DDLCommands ([date], [user], [servername], db, [event], [application], [hostname], [EventType], [SchemaName], [ObjectName], [ObjectType], [OriginalUser])
VALUES (getdate(), SYSTEM_USER, cast(SERVERPROPERTY('servername') AS VARCHAR), DB_NAME(),  @s, APP_NAME(), HOST_NAME(), @EventType, @SchemaName, @ObjectName, @ObjectType, ORIGINAL_LOGIN())

GO

ENABLE TRIGGER [ChangeLogging_LogDDLCommands] ON DATABASE     
GO

  ----------------------------------------------------------------------------------

--DB level trigger, FOR sql 2008
CREATE TRIGGER [ChangeLogging_LogDDLCommands]                                
ON DATABASE
FOR 

 CREATE_ASSEMBLY,
 ALTER_ASSEMBLY,
 DROP_ASSEMBLY,
 CREATE_ASYMMETRIC_KEY,
 ALTER_ASYMMETRIC_KEY,
 DROP_ASYMMETRIC_KEY,  
 --ALTER_AUTHORIZATION,
 --ALTER_AUTHORIZATION_DATABASE,
 CREATE_CERTIFICATE,
 ALTER_CERTIFICATE,
 DROP_CERTIFICATE,
 CREATE_CONTRACT, 
 DROP_CONTRACT, 
  --CREATE_CREDENTIAL,
  --ALTER_CREDENTIAL
  --DROP_CREDENTIAL
 GRANT_DATABASE,  
 DENY_DATABASE,
 REVOKE_DATABASE,
 CREATE_DEFAULT,
 DROP_DEFAULT,
 BIND_DEFAULT,
 UNBIND_DEFAULT,
 CREATE_EVENT_NOTIFICATION,
 DROP_EVENT_NOTIFICATION,  
 CREATE_EXTENDED_PROPERTY,
 ALTER_EXTENDED_PROPERTY,
 DROP_EXTENDED_PROPERTY,
 CREATE_FULLTEXT_CATALOG,
 ALTER_FULLTEXT_CATALOG,
 DROP_FULLTEXT_CATALOG,
 CREATE_FULLTEXT_INDEX,
 ALTER_FULLTEXT_INDEX,  
 DROP_FULLTEXT_INDEX,
 CREATE_FUNCTION,
 ALTER_FUNCTION,
 DROP_FUNCTION,
 CREATE_INDEX,
 --ALTER_INDEX,
 DROP_INDEX,
 CREATE_MASTER_KEY,
 ALTER_MASTER_KEY,
 DROP_MASTER_KEY,
 CREATE_MESSAGE_TYPE,
 ALTER_MESSAGE_TYPE,  
 DROP_MESSAGE_TYPE,
 CREATE_PARTITION_FUNCTION,
 ALTER_PARTITION_FUNCTION,
 DROP_PARTITION_FUNCTION,
 CREATE_PARTITION_SCHEME,
 ALTER_PARTITION_SCHEME,
 DROP_PARTITION_SCHEME, 
 CREATE_PLAN_GUIDE,
 ALTER_PLAN_GUIDE,
 DROP_PLAN_GUIDE,
 CREATE_PROCEDURE,
 ALTER_PROCEDURE,
 DROP_PROCEDURE, 
 CREATE_QUEUE,
 ALTER_QUEUE,
 DROP_QUEUE,
 CREATE_REMOTE_SERVICE_BINDING,
 ALTER_REMOTE_SERVICE_BINDING,
 DROP_REMOTE_SERVICE_BINDING,
 CREATE_SPATIAL_INDEX,
 RENAME, 
 CREATE_ROLE,
 ALTER_ROLE,
 DROP_ROLE,
 ADD_ROLE_MEMBER,
 DROP_ROLE_MEMBER, 
 CREATE_ROUTE,
 ALTER_ROUTE,
 DROP_ROUTE,
 CREATE_RULE,
 DROP_RULE,
 BIND_RULE,
 UNBIND_RULE, 
 CREATE_SCHEMA,
 ALTER_SCHEMA,
 DROP_SCHEMA,
 CREATE_SERVICE,
 ALTER_SERVICE,  
 DROP_SERVICE,
  --ALTER_SERVICE_MASTER_KEY
 ADD_SIGNATURE,
 DROP_SIGNATURE,    
 CREATE_STATISTICS,
 DROP_STATISTICS,
 --UPDATE_STATISTICS,  
 CREATE_SYMMETRIC_KEY,
 ALTER_SYMMETRIC_KEY,
 DROP_SYMMETRIC_KEY,
 CREATE_SYNONYM,
 DROP_SYNONYM,
 CREATE_TABLE, 
 ALTER_TABLE,
 DROP_TABLE,
 CREATE_TRIGGER,
 ALTER_TRIGGER,
 DROP_TRIGGER,
 CREATE_TYPE,  
 DROP_TYPE,
 CREATE_USER,
 ALTER_USER,
 DROP_USER,
 CREATE_VIEW,
 ALTER_VIEW,
 DROP_VIEW, 
 CREATE_XML_INDEX,
 CREATE_XML_SCHEMA_COLLECTION,
 ALTER_XML_SCHEMA_COLLECTION,
 DROP_XML_SCHEMA_COLLECTION
 
AS                                                      

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET NUMERIC_ROUNDABORT OFF

DECLARE @s VARCHAR(MAX)
DECLARE @EventType VARCHAR(MAX)
DECLARE @SchemaName VARCHAR(MAX)
DECLARE @ObjectName VARCHAR(MAX)
DECLARE @ObjectType VARCHAR(MAX)

SET @s = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') 
SET @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)') 
SET @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
SET @ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')

--INSERT INTO Utils.dbo.ChangeLogging_DDLCommands ([date], [user], db, [event], [application], [hostname], [EventType], [SchemaName], [ObjectName], [ObjectType], [OriginalUser])
-- VALUES (getdate(), SYSTEM_USER, DB_NAME(), @s, APP_NAME(), HOST_NAME(), @EventType, @SchemaName, @ObjectName, @ObjectType, ORIGINAL_LOGIN())

INSERT INTO Utils.dbo.ChangeLogging_DDLCommands ([date], [user], [servername], db, [event], [application], [hostname], [EventType], [SchemaName], [ObjectName], [ObjectType], [OriginalUser])
VALUES (getdate(), SYSTEM_USER, cast(SERVERPROPERTY('servername') AS VARCHAR), DB_NAME(),  @s, APP_NAME(), HOST_NAME(), @EventType, @SchemaName, @ObjectName, @ObjectType, ORIGINAL_LOGIN())

GO

ENABLE TRIGGER [ChangeLogging_LogDDLCommands] ON DATABASE                      
GO

  ----------------------------------------------------------------------------------

 

 

 

--Server level trigger, FOR sql 2005, AHEAD THE ONE FOR 2008
--(the change stands on the list of events trapped)
CREATE TRIGGER [ChangeLogging_LogDDLServerCommands]
ON ALL SERVER
FOR 

ALTER_AUTHORIZATION_SERVER,
  
CREATE_DATABASE,
 ALTER_DATABASE,
 DROP_DATABASE,
 
CREATE_ENDPOINT,
 DROP_ENDPOINT,
 
CREATE_LOGIN,
 ALTER_LOGIN,
 DROP_LOGIN,
 
GRANT_SERVER,
 DENY_SERVER,
 REVOKE_SERVER
 
AS                                          

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET NUMERIC_ROUNDABORT OFF

DECLARE @s VARCHAR(MAX)
DECLARE @EventType VARCHAR(MAX)
DECLARE @SchemaName VARCHAR(MAX)
DECLARE @ObjectName VARCHAR(MAX)
DECLARE @ObjectType VARCHAR(MAX)

SET @s = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') 
SET @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)') 
SET @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
SET @ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')

INSERT INTO Utils.dbo.ChangeLogging_DDLCommands ([date], [user], [servername], db, [event], [application], [hostname], [EventType], [SchemaName], [ObjectName], [ObjectType], [OriginalUser])
 VALUES (getdate(), SYSTEM_USER, cast(SERVERPROPERTY('servername') AS VARCHAR), NULL,  @s, APP_NAME(), HOST_NAME(), @EventType, @SchemaName, @ObjectName, @ObjectType, ORIGINAL_LOGIN())
GO

ENABLE TRIGGER [ChangeLogging_LogDDLServerCommands] ON ALL SERVER
GO

  ----------------------------------------------------------------------------------

 

--Server level trigger, FOR sql 2008
CREATE TRIGGER [ChangeLogging_LogDDLServerCommands]
ON ALL SERVER
FOR 
 ALTER_AUTHORIZATION_SERVER,
 CREATE_DATABASE,
 ALTER_DATABASE,
 DROP_DATABASE,
 CREATE_ENDPOINT,
 ALTER_ENDPOINT,
 DROP_ENDPOINT,
 CREATE_EXTENDED_PROCEDURE,
 DROP_EXTENDED_PROCEDURE,
 ALTER_INSTANCE,
 CREATE_LINKED_SERVER,
 ALTER_LINKED_SERVER,
 DROP_LINKED_SERVER,
 CREATE_LINKED_SERVER_LOGIN,
 DROP_LINKED_SERVER_LOGIN,
 CREATE_LOGIN,
 ALTER_LOGIN,
 DROP_LOGIN,
 CREATE_MESSAGE,
 ALTER_MESSAGE,
 DROP_MESSAGE,
 CREATE_REMOTE_SERVER,
 ALTER_REMOTE_SERVER,
 DROP_REMOTE_SERVER,
 GRANT_SERVER,
 DENY_SERVER,
 REVOKE_SERVER,
 ADD_SERVER_ROLE_MEMBER,
 DROP_SERVER_ROLE_MEMBER 
AS

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET NUMERIC_ROUNDABORT OFF

DECLARE @s VARCHAR(MAX)
DECLARE @EventType VARCHAR(MAX)
DECLARE @SchemaName VARCHAR(MAX)
DECLARE @ObjectName VARCHAR(MAX)
DECLARE @ObjectType VARCHAR(MAX)

SET @s = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') 
SET @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)') 
SET @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
SET @ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')

INSERT INTO Utils.dbo.ChangeLogging_DDLCommands ([date], [user], [servername], db, [event], [application], [hostname], [EventType], [SchemaName], [ObjectName], [ObjectType], [OriginalUser])
 VALUES (getdate(), SYSTEM_USER, cast(SERVERPROPERTY('servername') AS VARCHAR), NULL,  @s, APP_NAME(), HOST_NAME(), @EventType, @SchemaName, @ObjectName, @ObjectType, ORIGINAL_LOGIN())
GO

ENABLE TRIGGER [ChangeLogging_LogDDLServerCommands] ON ALL SERVER
GO

  ----------------------------------------------------------------------------------

Answer 4

> Yes, see if this helps, it's really fully tested, follow instructions and let me know what you think, apply before in test.

Excuse me, but I get the feeling that there is a "not" missing there: it's not really fully tested. Am I right? Don't feel ashamed - I am notorious for making that mistake myself.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

Hello Erland

I take the occasion to thankyou for your great beta_lockinfo !

The scrips are FULLY tested, they work in critical environment as well, try them and let me know, I would relly appreciate.

Please come back to me if you need any explanation.

Regards,

        Marco

 

 

Answer 6

Hi Marco,  I will try your DDL audit sometime. I have been using another one that a blogger started...I had to tweak it quite a bit. I'm finding the real test is whether or not you can install System Center 2012 products with the trigger enabled. That will really run any server triggers you have through the ringer. They use a lot of very long schema and table names, and they used advanced functions like XML datatypes and indexed views I believe. They will also have multi-language unicode inserts so will route out any nvarchar vs varchar issues also...I kept having installers fail when trying to use a ddl audit trigger like the following.

I too would like a way to track all DDL audits, but we would also like a way to track all DDL by all users and DML by role=sysadmin. I haven't found away to do DML by Sysadmin only yet. I would really like if the server audit specification could at least to the DDL auditing by all users, capturing the full DDL change code. I haven't figured out how or if that can be done. It would be safer and more reliable than DDL audit server triggers if it can be done.

I borrowed and modified this code. I've still had some issues with System Center App X 2012 installs with the trigger enabled...get all sorts of ANSI_Padding,ANSI_NULLS, session errors, etc. I've never been able to figure out the right combination that ALWAYS works.

110713 Edit: I changed select client_net_adress to select top 1 client_net_address as shown. Some MS and other app installers(SCOM) may open multiple conns to do a lot of ddl changes, the trigger can interfere if you don't change this top 1. Other than that I have been using this trigger successfully on numerous 2008R2 systems with a very wide variety of vendor apps and installers/upgraders. There are a few versions of this approach , and I certainly borrowed my original code from others, but so far, I think my whittled approach seen below is more inclusive, reliable, and simpler than others.

http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-%E2%80%93-auditing-schema-changes-using-ddl-triggers/

--assuming you have a util db named dbops or whatever

--create a table in your util db
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[audit_ddl_log](
[EventTime] [datetime] NULL,
[LoginName] [nvarchar](50) NULL,
[UserName] [nvarchar](255) NULL,
[DatabaseName] [nvarchar](255) NULL,
[SchemaName] [nvarchar](255) NULL,
[ObjectName] [nvarchar](255) NULL,
[ObjectType] [nvarchar](50) NULL,
[DDLCommand] [nvarchar](max) NULL,
[Client_IP] [nvarchar](50) NULL
)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--using nvarchar wastes space but it saves issues with ansi_padding
--many ms apps use indexed views and xml datatypes and the ansi_padding
--issue causes trigger to bomb which causes calling DDL app upgrade to fail
--etc.   I think just using nvarchar is the best catchall because
--that datatype is always ansi_padding on. MS says ansi_padding
--will soon be ON by default in next versions of SQL.
--this issue has driven me crazy with this trigger so far, but this seems
--to maybe work...--Forrest 060512
CREATE TRIGGER [Log_Table_DDL] ON ALL SERVER WITH EXECUTE AS 'sa'
FOR DDL_DATABASE_LEVEL_EVENTS,DDL_SERVER_LEVEL_EVENTS
AS 
DECLARE       @eventInfo XML
DECLARE       @client_net varchar(50)
SET           @eventInfo = EVENTDATA()
SET @client_net=(select top 1 client_net_address from master.sys.dm_exec_connections WHERE session_id = @@spid)
INSERT INTO dbops.dbo.audit_ddl_log VALUES
 
(
 
       REPLACE(CONVERT(NVARCHAR(50),
 
              @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
 
       CONVERT(NVARCHAR(50),
 
              @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),
 
       CONVERT(NVARCHAR(255),
 
              @eventInfo.query('data(/EVENT_INSTANCE/UserName)')),
 
       CONVERT(NVARCHAR(255),
 
              @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),
 
       CONVERT(NVARCHAR(255),
 
              @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),
 
       CONVERT(NVARCHAR(255),
 
              @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),
 
       CONVERT(NVARCHAR(50),
 
              @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),
 
       CONVERT(NVARCHAR(MAX),
 
              @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
		
		,@client_net
) 
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
--enable here if you are ready to test
--it is a good idea to test a DAC first
--if you ever drop,corrupt...the table in util db, 
--there will be errors and users will not be able to 
--make changes...the inherent danger of instance wide 
--triggers
--change to ENABLED in SSMS etc when ready
--but have this command ready via DAC for emergencies
DISABLE TRIGGER [Log_Table_DDL] ON ALL SERVER
GO

             

Answer 7

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Pray tell, how did these commands come in here? Please don't tell me that they were generated by SSMS, because that should never happen!

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Hi Erland,

Yes I scripted from SSMS 10.50.2811 and it does put those in. I'm not sure if relates to your instance defaults or not, but I assume it is just turning it on to be sure for the session while you create this item and then shutting it off just for the session. 

Ugh...all of these settings have been a problem recently...ANSI_NULLS, QUOTED_IDENTIFIERS, ANSI_PADDING. Trying to create a lot of indexing jobs and supporting apps that leverage advanced SQL schema features(MS System Center, VMWare vSphere) have all been troublesome because of these settings. I think XML datatypes, indexed views have complicated some of these settings. I think the general rule is you want it all consistent...table creation..index creation, population, maintenance, session creation...but I am still struggling to solve some of these issues. It's hard when the app is a blackbox.

F.

---

UC Berkeley

Answer 9

Yes, you should always make sure that you have these settings on: ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING, ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL, since there is functionality that require these settings to be on.

For the last two, there is only a run-time setting to bother about. However, the first two are saved with stored procedures and other modules, so that the saved setting applies. And the setting of ANSI_PADDING is save per column.

All these setting are legacy settings, only to be used with very old software. I thought SSMS was clean from adding SET OFF commands for ANSI_NULLS and QUOTED_IDENTIFIER, but I need to investigate this.

You can run queries against the catalog views to find object that have incorrect settings. Please let me know if you want help with such queries.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 10

Do you have people other than sysadmins doing CREATE, ALTER. or DROP?  Why wouldn't you want to look for anyone doing these functions?