Annual company audit - Demonstrating the controls and approvals

bboppel

Hello - Every year our company goes through an internal audit process.  I'm asked to provide a list of all database schema changes that occurred in the past year.  The auditor picks a random sampling of those changes then asks me to provide the "sign-offs" for each one they selected.

To do this, the auditor has access to DLM Dashboard and hunts/pecks for a sampling of changes.  I then match their sampling list up with signature approvals that I keep throughout the year.

This all seems very manual and am wondering what other folks out their are doing (if anything).

Thanks for reading and I appreciate any thoughts/comments you may have on this.

Jessica R

Hi @bboppel,

Sorry to say that Redgate doesn't have any auditing tools to help with this.

Hopefully a community member may have some suggestions!

IanJerrett

I have just queried the xml in the eventdata column within the Redgate database.  This is fundamentally where DLM gets the data from to display. the catch is you have to connect to each server/listener that DLM is monitoring.

It would be much better if Redgate would do two things:
1. Provide a summary of changes before you drill into the detail, the same way you do in SQL Compare.
2. Provide a report that summarises all changes made with the option to drill through on any change

IanJerrett

I forgot to add the code, although not the most eloquent.

SELECT id,PostTime

      ,eventdata.value('(/EVENT_INSTANCE/EventType/.)[1]', 'varchar(50)') as EventType

      ,eventdata.value('(/EVENT_INSTANCE/ObjectName/.)[1]', 'varchar(max)') as [ObjectName]

,eventdata.value('(/EVENT_INSTANCE/SchemaName/.)[1]', 'varchar(max)') as SchemaName

,eventdata.value('(/EVENT_INSTANCE/ObjectType/.)[1]', 'varchar(max)') as [ObjectType]

  ,eventdata.value('(/EVENT_INSTANCE/ServerName/.)[1]', 'varchar(max)') as ServerName

,eventdata.value('(/EVENT_INSTANCE/LoginName/.)[1]', 'varchar(max)') as LoginName

,eventdata.value('(/EVENT_INSTANCE/UserName/.)[1]', 'varchar(max)') as UserName

,eventdata.value('(/EVENT_INSTANCE/DatabaseName/.)[1]', 'varchar(max)') as DatabaseName

      ,eventdata.value('(/EVENT_INSTANCE/TSQLCommand/.)[1]', 'varchar(max)')as [TSQL]

from   SQLLighthouse.DDL_Events

ORDER BY PostTime DESC