The Multifunctioning DBA

Jan 30 2011   9:21PM GMT

Triggers for protection

Colin Smith Colin Smith Profile: Colin Smith

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
(PostTime datetime,
DatabaseName varchar(100),
Event nvarchar(100),
ObjectName varchar(256),
TSQL nvarchar(2000),
Login varchar(100))
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
for drop_login
as
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’
rollback

INSERT INTO DDLAudit (PostTime, DatabaseName, Event, ObjectName, TSQL,Login)
VALUES
(
GetDate(),
@ed.value(‘(/EVENT_INSTANCE/DatabaseName)[1]‘, ‘varchar(256)’),
@ed.value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘nvarchar(100)’),
@ed.value(‘(/EVENT_INSTANCE/ObjectName)[1]‘, ‘varchar(256)’),
@ed.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(2000)’),
@ed.value(‘(/EVENT_INSTANCE/LoginName)[1]‘, ‘varchar(256)’)
)

go
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
Enable trigger_name
on all server
That way you, as the DBA, can make the changes you need to make.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: