Showing posts with label DELETE. Show all posts
Showing posts with label DELETE. Show all posts

Monday, November 07, 2016

Why you need additional privileges to truncate tables compared to delete statements



One of the people on our team wanted to have the ability to truncate tables on the staging database while this person was doing some testing.

 Here is what Books On Line has about permissions for the TRUNCATED statement

The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

Before I answer why someone would need ALTER TABLE permissions when the person already has DELETE permissions, let’s run some code that will show the ‘problem’.

CREATE DATABASE Test
go
 
USE Test
GO
 
CREATE TABLE TestTruncate(Id int)
GO
 
INSERT TestTruncate values(1)
GO

Now create a new user and give the user datareader and datawriter permissions

USE master
GO
CREATE LOGIN TestLogin WITH PASSWORD=N'Test', 
DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE Test
GO
CREATE USER TestLogin FOR LOGIN TestLogin
GO
USE Test
GO
ALTER ROLE db_datareader ADD MEMBER TestLogin
GO
USE Test
GO
ALTER ROLE db_datawriter ADD MEMBER TestLogin
GO

Now that the user is created, login as that user and run the TRUNCATE TABLE command


TRUNCATE TABLE TestTruncate

Msg 1088, Level 16, State 7, Line 1
Cannot find the object “TestTruncate” because it does not exist or you do not have permissions.
As you can see, you don’t have permission. Executing a delete will work just fine


DELETE TestTruncate


(1 row(s) affected)

Before I give you a workaround, let’s try to figure out why the minimum requirement is ALTER TABLE.
What is the difference between a DELETE and a TRUNCATE in terms of logging? When a TRUNCATE occurs, the operation does not log individual row deletions, a DELETE operation does. The reason this is important is because if you have a trigger on the table, in needs to be disabled before the TRUNCATE occurs. Now you know why ALTER TABLE is required, triggers need to be disabled.


ALTER TABLE SomeTable DISABLE TRIGGER SomeTrigger



And in order to disable the trigger, ALTER TABLE permissions are required as a minimum.

But I don’t want people altering tables on our staging and QA servers, so here is one way of giving the person the ability to TRUNCATE a table without giving them permissions explicitly. Create a stored procedure and use WITH EXECUTE AS, this will define the execution context of the stored procedure. In the example below, I picked a user that has sufficient privileges to perform the TRUNCATE.



CREATE PROCEDURE prTruncate
WITH EXECUTE AS 'SuperUser'
AS
TRUNCATE TABLE TestTruncate
GO


All you have to do is give your user execute permissions to the stored procedure you just created



GRANT EXECUTE ON prTruncate TO TestLogin
GO


Now if you execute the stored procedure as the TestLogin user, you will see it will run just fine



EXEC prTruncate

Hope this helps someone in the future who is filling up his or her transaction log these days with all those DELETE statements

Wednesday, June 13, 2007

SQL Myth: Truncate Cannot Be Rolled Back Because It Is Not Logged

I am still amazed at how many people still think that TRUNCATE TABLE is not logged. There is some logging going on but it is minimal, here is what Books On Line says:

TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.

Let’s prove that we can rollback a truncate

Create this table and do the select

CREATE TABLE dbo.Enfarkulator (ID int IDENTITY PRIMARY KEY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator VALUES(1)



SELECT * FROM dbo.Enfarkulator

ID SomeOtherCol
1 1
2 1


Now run this part

BEGIN TRAN
TRUNCATE TABLE
dbo.Enfarkulator
SELECT * FROM dbo.Enfarkulator
ROLLBACK TRAN


ID SomeOtherCol
(0 row(s) affected)

As you can see the table was truncated, now select from the table again


SELECT * FROM dbo.Enfarkulator

ID SomeOtherCol
1 1
2 1


Yep, the data is there, proving that you can rollback a truncate and all the data will be there. There are two other major difference between truncate and delete which I will explain below.

Truncate doesn’t preserve the identity value but delete does

This is another difference between truncate and delete, truncate will reset the identity value but delete does not. Run the following code to see how that works


CREATE TABLE dbo.Enfarkulator2 (ID int IDENTITY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator2 VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)


SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator


DELETE dbo.Enfarkulator2
TRUNCATE TABLE dbo.Enfarkulator

INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)

SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator

The Enfarkulator id was reset and the Enfarkulator2 id was not. In order to do the same with delete you will need to run a dbcc checkident reseed command. Here is the code for that.

DELETE dbo.Enfarkulator2
TRUNCATE TABLE dbo.Enfarkulator

DBCC CHECKIDENT (Enfarkulator2, RESEED, 0)

Now insert again and you will see that the values are the same.

INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)

SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator



You can’t truncate tables that are referenced by a foreign key constraint.

If you have a table which is referenced by another table with a foreign key constraint then you cannot truncate that table. Here is the code for that

CREATE TABLE dbo.Enfarkulator3 (ID int IDENTITY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator3 VALUES(1)



Now let’s add the foreign key

ALTER TABLE dbo.Enfarkulator3 ADD CONSTRAINT [FK_Fark3_Fark]
FOREIGN KEY ([ID]) REFERENCES [dbo].[Enfarkulator] ([ID])


Now try to truncate.

TRUNCATE TABLE Enfarkulator

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Enfarkulator' because it is being referenced by a FOREIGN KEY constraint.

See? You cannot do that

--Clean up time ;-)
DROP TABLE dbo.Enfarkulator3,dbo.Enfarkulator2,dbo.Enfarkulator


Cross-posted from SQLBlog! - http://www.sqlblog.com/