June 2, 2010 at 2:19 pm
I have a trigger (a tattler) that we use to stop people from adding, deleting & modifying databases at will. The Adding & Deleting part of the trigger works fine but I just found out that the ALTER_DATABASE, although firing, does not stop the renaming of a database. It still allows the rename to take place.
I've been using ROLLBACK and that works fine for the CREATE_DATABASE and the DROP_DATABASE. But doesn't seem to work when the ALTER_DATABASE fires off. I've tried ROLLBACK, ROLLBACK TRAN, ROLLBACK TRANSACTION, ROLLBACK IMMEDIATE. All of which either don't work or error out.
Any ideas? Thanks!
June 3, 2010 at 10:54 am
June 3, 2010 at 11:15 am
This following code executes with no problems. I get the print statements & the email (when it's not commented out) but the ROLLBACK doesn't work. After sleeping on this issue I've come to the conclusion that changing the database name doesn't constitute changing the data itself within the database hence why it doesn't "Rollback". The ROLLBACK affects transactions hence why it may not stop the database from being renamed.
Still....you would think there would be a way to counter this.
/****** Object: DdlTrigger [STOP_DB_CREATE_ON_SERVER] Script Date: 06/02/2010 16:05:51 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
alter TRIGGER [STOP_DB_CREATE_ON_SERVER] ON ALL SERVER
FOR CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
AS
BEGIN
DECLARE @LoginName VARCHAR(50) ,
@SubjectText NVARCHAR(100) ,
@BodyText NVARCHAR(500) ,
@DateTime NVARCHAR(50)
SET @LoginName = ORIGINAL_LOGIN()
If @LoginName <> xxxxxxxxxx\jwaclawski' AND @LoginName <> 'xxxxxxxxxx\someoneelse'
BEGIN
PRINT '*************************************************************'
PRINT 'If you need to Add/Delete/Modify a database on this server,'
PRINT 'please see Dan or John.'
PRINT '---- Command not completed. ----'
PRINT '*************************************************************'
ROLLBACK
SET @SubjectText = 'Database Create/Alter/Deletion attempt on server: '
+ @@ServerName
SET @LoginName = ORIGINAL_LOGIN()
SET @BodyText = 'Database Create/Alter/Deletion' + CHAR(13)
+ CHAR(13) + 'Server:' + @@ServerName + CHAR(13)
+ 'Login:' + @LoginName + CHAR(13)
--Exec msdb.dbo.sp_send_dbmail
--@profile_name = 'SQLMailProfile',
--@recipients = 'email address goes here',
--@subject = @SubjectText,
--@body = @BodyText
--ROLLBACK
END
END
ROLLBACK
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [STOP_DB_CREATE_ON_SERVER] ON ALL SERVER
June 3, 2010 at 12:41 pm
John Waclawski (6/3/2010)
PRINT '*************************************************************'PRINT 'If you need to Add/Delete/Modify a database on this server,'
PRINT 'please see Dan or John.'
PRINT '---- Command not completed. ----'
PRINT '*************************************************************'
:w00t: man, you are surrounded by very dangerous people 😀
Have you considered locking down servers and enforcing security policies?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 3, 2010 at 12:42 pm
Duplicate post
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 3, 2010 at 12:48 pm
PaulB-TheOneAndOnly (6/3/2010)
John Waclawski (6/3/2010)
PRINT '*************************************************************'PRINT 'If you need to Add/Delete/Modify a database on this server,'
PRINT 'please see Dan or John.'
PRINT '---- Command not completed. ----'
PRINT '*************************************************************'
:w00t: man, you are surrounded by very dangerous people 😀
Have you considered locking down servers and enforcing security policies?
These are the development servers. (for the most part). But security aside (I guess I should have anticipated a reply such as this), is there a way to fix this using a trigger & t-sql?
Thank you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply