July 20, 2016 at 11:26 am
Hey guys, had one of my team members drop our main table by accident. Is there a way to prevent a table from being truncated or dropped? I tried adding a foreign key which allows nulls in a test table but it allowed me to truncate the table.
July 20, 2016 at 12:09 pm
The foreign key should allow you to prevent truncates. To prevent dropping tables, you can use the a DDL trigger.
CREATE TABLE TriggerTest( id int)
GO
CREATE TRIGGER T_Avoid_Drop_Table ON DATABASE
FOR DROP_TABLE
AS
PRINT 'You must disable a trigger to drop tables!'
ROLLBACK TRANSACTION
GO
DROP TABLE TriggerTest
GO
DISABLE TRIGGER T_Avoid_Drop_Table ON DATABASE
GO
DROP TABLE TriggerTest;
IF OBJECT_ID( 'TriggerTest') IS NULL
PRINT 'Success';
GO
DROP TRIGGER T_Avoid_Drop_Table ON DATABASE
July 20, 2016 at 12:26 pm
dndaughtery (7/20/2016)
Hey guys, had one of my team members drop our main table by accident. Is there a way to prevent a table from being truncated or dropped? I tried adding a foreign key which allows nulls in a test table but it allowed me to truncate the table.
To best answer this question we need some context about where this needs to be done and who we are attempting to block.
First, dropping or truncating requires either DB_OWNER database role, SYSADMIN server role, or explicit permissions granted to user on the object. None of these should normally be granted to a user in the production environment. If this is a production environment then read up on best practices regarding how to enforce "least privilege", which you can find in various articles and posts here in SQLServerCentral.
Are you asking how to prevent certain types of operations from occurring in development where users can have elevated permissions?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 20, 2016 at 1:18 pm
Quick thought, foreign key constraint to the table from a pseudo table on a column with a default value constraint will do the trick.
😎
Edit: Typo
July 20, 2016 at 2:11 pm
No more tears.
ALTER SERVER ROLE [sysadmin] DROP MEMBER [<USER_NAME>];
USE <DB_NAME>;
ALTER ROLE [db_owner] DROP MEMBER [<USER_NAME>];
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 23, 2016 at 1:15 am
This was removed by the editor as SPAM
August 23, 2016 at 5:34 am
Eric M Russell (7/20/2016)
dndaughtery (7/20/2016)
Hey guys, had one of my team members drop our main table by accident. Is there a way to prevent a table from being truncated or dropped? I tried adding a foreign key which allows nulls in a test table but it allowed me to truncate the table.To best answer this question we need some context about where this needs to be done and who we are attempting to block.
First, dropping or truncating requires either DB_OWNER database role, SYSADMIN server role, or explicit permissions granted to user on the object. None of these should normally be granted to a user in the production environment. If this is a production environment then read up on best practices regarding how to enforce "least privilege", which you can find in various articles and posts here in SQLServerCentral.
Are you asking how to prevent certain types of operations from occurring in development where users can have elevated permissions?
ALTER on the schema will also provide the necessary permission too so check for that
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 23, 2016 at 6:55 am
JasonClark (8/23/2016)
Try to run the below code to prevent accidental delete:
CREATE TRIGGER [TR_ProtectCriticalTables]ON DATABASE
FOR
DROP_TABLE
AS
DECLARE @eventData XML,
@uname NVARCHAR(50),
@oname NVARCHAR(100),
@otext VARCHAR(MAX),
@etype NVARCHAR(100),
@edate DATETIME
SET @eventData = eventdata()
SELECT
@edate=GETDATE(),
@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)'),
@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
IF @oname IN ('tblBananas','tblApples','tblOranges')
BEGIN
DECLARE @err varchar(100)
SET @err = 'Table ' + @oname + ' is super duper protected and cannot be dropped.'
RAISERROR (@err, 16, 1) ;
ROLLBACK;
END
GO
ENABLE TRIGGER [TR_ProtectCriticalTables] ON DATABASE
Lowell? Isn't this yours?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 23, 2016 at 8:17 am
Depending on how strong of a lock you want - the low end could be as simple as putting up a schema-bound view that references the table you don't want dropped.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 23, 2016 at 8:54 am
ChrisM@Work (8/23/2016)
JasonClark (8/23/2016)
Try to run the below code to prevent accidental delete:
CREATE TRIGGER [TR_ProtectCriticalTables]ON DATABASE
FOR
DROP_TABLE
AS
DECLARE @eventData XML,
@uname NVARCHAR(50),
@oname NVARCHAR(100),
@otext VARCHAR(MAX),
@etype NVARCHAR(100),
@edate DATETIME
SET @eventData = eventdata()
SELECT
@edate=GETDATE(),
@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)'),
@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
IF @oname IN ('tblBananas','tblApples','tblOranges')
BEGIN
DECLARE @err varchar(100)
SET @err = 'Table ' + @oname + ' is super duper protected and cannot be dropped.'
RAISERROR (@err, 16, 1) ;
ROLLBACK;
END
GO
ENABLE TRIGGER [TR_ProtectCriticalTables] ON DATABASE
Lowell? Isn't this yours?
yes it is Chris; it's a decent example from a post year or so ago: http://www.sqlservercentral.com/Forums/Topic1141863-338-1.aspx
Lowell
August 24, 2016 at 1:34 pm
Eric M Russell (7/20/2016)
dndaughtery (7/20/2016)
Hey guys, had one of my team members drop our main table by accident. Is there a way to prevent a table from being truncated or dropped? I tried adding a foreign key which allows nulls in a test table but it allowed me to truncate the table.To best answer this question we need some context about where this needs to be done and who we are attempting to block.
First, dropping or truncating requires either DB_OWNER database role, SYSADMIN server role, or explicit permissions granted to user on the object. None of these should normally be granted to a user in the production environment. If this is a production environment then read up on best practices regarding how to enforce "least privilege", which you can find in various articles and posts here in SQLServerCentral.
Are you asking how to prevent certain types of operations from occurring in development where users can have elevated permissions?
This was also my thought. We do not allow anyone to have anything other than read access to production data with their everyday ID. DBA's have special ID's with the rights to alter data, and they only run scripts that have been promoted through an approval process. This really minimizes the risk of inadvertent data loss.
August 25, 2016 at 12:40 pm
Regardless of the method used to prevent a table drop, there should still be regularly practiced drills where you assume a catastrophe of some sort. That means disaster recovery drills where you back up the log, ..., and do a test restore from backup. This just to make sure you have all the steps down like clockwork to reduce data loss and minimise downtime. You also never know when a large back up might be corrupt. You dont want to know this when you actually need it.
----------------------------------------------------
August 25, 2016 at 1:58 pm
Eric M Russell (7/20/2016)
No more tears.
ALTER SERVER ROLE [sysadmin] DROP MEMBER [<USER_NAME>];
USE <DB_NAME>;
ALTER ROLE [db_owner] DROP MEMBER [<USER_NAME>];
Too funny. I'm surprised nobody else picked up on this one.
August 26, 2016 at 2:09 pm
In all seriousness, I second this one. If this user's job does not include dropping or truncating tables, he/she should not have the right. If they are involved in moving code to the server, then a different solution is needed.
EDIT: I should have hit "quote" instead of reply. This is in reference to the "no more tears" comment.
August 26, 2016 at 2:24 pm
Granting developers and other users membership in the SYSADMIN role is like planting a Tree Of Good And Evil in the middle of the Garden If Eden and then commanding everyone never to eat from it.
What do you think it going to happen?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply