Jan 30 2011 9:21PM GMT
Posted by: Colin Smith
, MS SQL Admin
I have recently had to find a way to prevent someone that has elevated privileges from removing logins, databases, as well as keep them from adding new databases to my production systems. I was amazed at how easy this was to do thanks to event level ddl triggers. I created a table in master called ddlaudit. The format is below:
create table DDLAUDIT
Now that I have a table to push data into I just needed to create the triggers. Below is one of them but I did create all three.
Create Trigger AUDIT_Drop_Login
On all server
Declare @audit XML
DECLARE @ed XML
SET @ed = EVENTDATA()
Print ‘You are not allowed to drop logins from this instance of SQL Server. Please contact the DBA’
INSERT INTO DDLAudit (PostTime, DatabaseName, Event, ObjectName, TSQL,Login)
Now that I have them create no one, not even me, can perform these actions. And when someone does attempt one of the actions you will have an audit trail of who did what and when they did it. Now if you want to be able to drop a login after this is in place you will need to disable or drop the trigger. You can find the trigger by running the following query.
select * from sys.server_triggers
Disable trigger trigger_name
on all server
on all server
That way you, as the DBA, can make the changes you need to make.