Real life scenario ~ I’ve seen companies that I’ve come into not have their production environment on lock down and every one who is anyone has access to the environment.
I like the idea of having a table with the following columns:
- AuditID – Unique Identifier
- AuditDate – Date the event occurred
- LoginName – Who did the event
- ObjectName – What object was affected
- Command – The command that was executed
- EventType – What kind of event occurred
- ServerName – The server the Event occurred on
- DatabaseName – Which Database the Event occurred on
Once the table is created you can create a database trigger that will dump data into your table so you can track who is doing what in the database at any given time. I’ve used this practice at previous job sites and was happy to see this being utilized at my current employer.
The trigger will look something similar to this:
CREATE TRIGGER [tr_Trigger Name]
SET NOCOUNT ON
DECLARE @EventData XML
SET @EventData = EVENTDATA()
INSERT INTO [DATABASE NAME].dbo.[TABLE NAME]
–Note you can add a where clause and exclude “Stats” if you so desire”
EXECUTE [DATABASE NAME].dbo.[SP TO CAPTURE ERRORS]
I’ve found this technique useful in running reports after deployments on what was deployed; going back for future reference on what was done, capturing issues among team members of not knowing what they were doing in order to correct it, and just for overall satisfaction of knowing what is occurring on the database itself.
If you’re not sure what is occurring on your databases then maybe this is something that you can try to help build a base to capture events that are occurring from drops, creates, to grants, etc.