MSSQL Server DDL Trigger For Prevent to Drop_Database,Create_Database Create_Login,Drop_Login for Particular Login

DDL triggers (auditing CREATE, ALTER, DROP and several system defined stored procedures which perform DDL operations) are often used for administrative tasks like regulating and auditing database operations and finally I got a chance to work one more interesting topic today and that is Trigger in SQL Server.

Trigger is really interesting thing in DBA’s life and it can give you lot for functionality and great security in very little code.

You can get easily all the information and code as well on internet and MSDN sites about triggers.

So what’s interesting here in my blog so let’s discuss about scenario about I am working J

Requirement:-

I wanted to create a DDL trigger that will Prevent to Drop_Database,Create_Database,Drop_Login and Create_Login for a particular logins not for all the user that  exist on that Server.

So, why we need to prevent only some Logins to perform this activity?

Actually my main requirement was to prevent drop a database accidently by DBA from my team cause some are junior DBA is also there and  DBA have all the rights so for safer side we want to make a better security.

We can make this process for all the Logins and it was really easy process if we want to prevent all the logins to perform DDL operation on server. But the issue is  why we didn’t do this cause we have lot of SQL Server and user who use different -different applications and whenever they setup any new application that application create a database in MSSQL Server automatically so if we setup this trigger server base then application team can’t setup application as well and they will start complaining to this like they have issue to setup application.so to avoid these kind of thing we want to create this trigger only for particular users that have main SYSADMIN rights.

So finally I write some code and test it against my local system and its working fine as per my expectation.

CREATE TRIGGER [TR_Prevent_CreateDrop_Database_Logins] ON all server FOR create_database,drop_database,create_login,drop_login AS
DECLARE @loginname nvarchar(100), 
  @eventData xml 
SET @eventData = eventdata() 
SELECT @LoginName= @eventData.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(50)') 
IF @loginname IN ('sa', 
                  'test') 
BEGIN 
  PRINT ' Are you sure to perform this actvity ??     If yes then Please disable the trigger "TR_Prevent_CreateDrop_Database_Logins" before perform this activity.' ROLLBACK;
end

go

enable TRIGGER [TR_Prevent_CreateDrop_Database_Logins] ON ALL server

Testing Phase:-

After executing above script trigger will create on below folder

Login with SA and tried to perform below steps:-

Now you can see the output of the above query and that we want J

Now we will try to run the same DDL command by different Logins [ASHISH-PC\ASHISH] that is not prevented by Trigger.

So, you can see the above out put its working fine J

It’s done!