August 15, 2014 at 3:50 pm
Adam Angelini (8/15/2014)
if @Command like '%MODIFY NAME%'
rollback;
This rollback is not what we think/expect
@@TRANCOUNT is incremented by one when entering a trigger, even when in autocommit mode. - http://technet.microsoft.com/en-us/library/ms181299(v=sql.105).aspx
ALTER DATABASE [Agents] MODIFY NAME = [Agents2]
passes and I don't know how to roll it back.
Igor Micev,My blog: www.igormicev.com
August 18, 2014 at 9:25 am
Igor Micev (8/15/2014)
...this is the code
ALTER TRIGGER ddl_trig_REnameON ALL SERVER
FOR RENAME
AS
BEGIN
DECLARE @data XML;
DECLARE @database nvarchar(100);
DECLARE @eventType varchar(50);
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]','sysname')
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')
SET @database = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)')
IF @database in ('Nova')
BEGIN
RAISERROR('You''re not allowed to rename this database',16,1)
ROLLBACK;
END
END
EXEC sp_renamedb 'Nova', 'Nova2'
GO
The first problem. The RENAME event does NOT apply to sp_renamedb, only to the sp_rename which renames objects like tables, views, stored procedures, etc. (See below).
Books Online (http://technet.microsoft.com/en-us/library/bb522542%28v=sql.100%29.aspx)
RENAME (Applies to sp_rename)
Books Online (sp_rename)
Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.
As far as preventing the rename of a database, I am working on an ALTER DATABASE trigger that will roll back any changes, but I have so far missed the mark. The database does indeed rename and doesn't roll back the rename even though it mentions that the DB can't be renamed. Trying to run ALTER DATABASE or sp_renameDB within the trigger results in the following errors:
The procedure 'sys.sp_renamedb' cannot be executed within a transaction.
...
ALTER DATABASE statement not allowed within multi-statement transaction.
August 18, 2014 at 11:13 am
Try as I might, I can't seem to get this to work. What really annoys me is those ALTER DATABASE and sp_renamedb messages. Looking at the text of sp_renamedb, it actually contains the ALTER DATABASE statement with a bunch of transactions and that obviously works. But it won't work in my trigger, even if I set implicit_transactions off.
Vaguely annoying. The only other solution I can think of to this is to simply log the changes to a table and create an email notification to the DBA team that the database name was changed and who changed it. Then the DBA team can manually make the rename modification.
August 18, 2014 at 11:35 am
Brandie Tarvin (8/18/2014)
Try as I might, I can't seem to get this to work. What really annoys me is those ALTER DATABASE and sp_renamedb messages. Looking at the text of sp_renamedb, it actually contains the ALTER DATABASE statement with a bunch of transactions and that obviously works. But it won't work in my trigger, even if I set implicit_transactions off.Vaguely annoying. The only other solution I can think of to this is to simply log the changes to a table and create an email notification to the DBA team that the database name was changed and who changed it. Then the DBA team can manually make the rename modification.
Yeah, I'll probably go with something similar.
Igor Micev,My blog: www.igormicev.com
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply