August 15, 2014 at 11:35 am
Hi
I'd like to implement something to prevent renaming some of the databases on the instance.
Is it possible in sql server?
Igor Micev,My blog: www.igormicev.com
August 15, 2014 at 11:47 am
Have you tried deny of alter on database?
August 15, 2014 at 11:50 am
djj (8/15/2014)
Have you tried deny of alter on database?
No, I need to alter the databases.
Igor Micev,My blog: www.igormicev.com
August 15, 2014 at 11:52 am
I did not mean DENY everyone just the logins (or group) you wish.
August 15, 2014 at 11:53 am
djj (8/15/2014)
I did not mean DENY everyone just the logins (or group) you wish.
Get you, but reject that option, because one Login is used for some databases... 🙁
Igor Micev,My blog: www.igormicev.com
August 15, 2014 at 11:54 am
Igor Micev (8/15/2014)
HiI'd like to implement something to prevent renaming some of the databases on the instance.
Is it possible in sql server?
Not entirely possible.
Policy Based Management won't prevent the name change of a database but will prevent the rename of other objects.
sysadmins can't be prevented from renaming a database.
The database owner cannot be prevented from renaming the database which it owns.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 15, 2014 at 12:03 pm
What about DDL Triggers? Could those rollback any attempt at renaming a database?
August 15, 2014 at 12:04 pm
It seems like impossible for my case.
However I could catch the event RENAME and know about. I just wanted to ensure no one is doing something strange.
Thanks anyway
Igor Micev,My blog: www.igormicev.com
August 15, 2014 at 12:06 pm
Brandie Tarvin (8/15/2014)
What about DDL Triggers? Could those rollback any attempt at renaming a database?
I have already tried with a DDL trigger on instance level to catch the RENAME event and do a rollback, but it didn't pass.
Igor Micev,My blog: www.igormicev.com
August 15, 2014 at 12:10 pm
You should be able to create a DDL trigger to prevent database renames, at least through ALTER DATABASE. I hope sp_renamedb would get caught as well, but I haven't tested that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 15, 2014 at 12:13 pm
Igor Micev (8/15/2014)
Brandie Tarvin (8/15/2014)
What about DDL Triggers? Could those rollback any attempt at renaming a database?I have already tried with a DDL trigger on instance level to catch the RENAME event and do a rollback, but it didn't pass.
What do you mean "it didn't pass"?
Could you post your DDL trigger code?
August 15, 2014 at 1:17 pm
Brandie Tarvin (8/15/2014)
Igor Micev (8/15/2014)
Brandie Tarvin (8/15/2014)
What about DDL Triggers? Could those rollback any attempt at renaming a database?I have already tried with a DDL trigger on instance level to catch the RENAME event and do a rollback, but it didn't pass.
What do you mean "it didn't pass"?
Could you post your DDL trigger code?
Yes, this is the code
ALTER TRIGGER ddl_trig_REname
ON 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
Igor Micev,My blog: www.igormicev.com
August 15, 2014 at 1:31 pm
--empty comment
Igor Micev,My blog: www.igormicev.com
August 15, 2014 at 1:32 pm
Shouldn't the event you want to intercept be "ALTER_DATABASE", not "RENAME"?
You'll need to interrogate the event data to identity a MODIFY NAME = vs other types of alter database commands.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 15, 2014 at 3:31 pm
I thought that would work too, however I tested this and it did not work:
ALTER trigger PreventRename on all server for alter_database as
declare @command varchar(max)
SELECT @Command = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
print @Command
if @Command like '%MODIFY NAME%'
rollback;
GO
sp_renamedb 'testdb','testdb2'
Results:
The database name 'testdb2' has been set.
ALTER DATABASE [testdb] MODIFY NAME = [testdb2]
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
It appears to rollback the transaction, but the database name change goes through.
Same results with this:
sp_rename 'testdb','testdb2','database'
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply