Delete OR Truncate

Question

Delete OR Truncate

VastSQL

SSC-Dedicated

Points: 36121
More actions
April 28, 2010 at 3:17 am

#218271

Hi Experts,
I have a small doubt regarding the permission ALTER.
By giving this we can truncate a table but we cant delete..Considering the security issues i think TRUNCATE is serious as its a no logged operation.
My doubt is why sql allowing truncate in ALTER and not delete.
Please Help
TIA

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29423 More actions · Answer 1

Ratheesh.K.Nair (4/28/2010)
Considering the security issues i think TRUNCATE is

Truncate is minimally logged operation. Rows affected by truncate operations are not logged, however, the log contains info that a particular table was truncated.

Pradeep Singh

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 2

Thanks Pradeep..

Can you please let me know why like this?? I wonder why Truncate remove all data which cannot be rolled back with just an ALTER PERMISSION

TIA

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Check out this QOTD, also check out the discussion that follows, all your questions will be answered there :

http://www.sqlservercentral.com/questions/T-SQL/69738/

http://www.sqlservercentral.com/Forums/Topic908285-1198-1.aspx

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 4

Thanks Ninja,

So TRUNCATE and DELETE are almost same then why its like TRUNCATE can be done by using an ALTER permission where as DELETE cannot and need specific permission?

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

I can't tell you specifically why truncate is allowed and not delete when you give the alter permission, but you're wrong about rollbacks. Truncate can be rolled back.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Grant Fritchey (4/28/2010)
I can't tell you specifically why truncate is allowed and not delete when you give the alter permission, but you're wrong about rollbacks. Truncate can be rolled back.

This could be attributed to granulity. Give the least amount of permissions to any user. You may need to be able to truncate a table to reload it from another environement. But without being allowed to delete 1 single row because you need to have both tables perfectly identical.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Truncate is generally more efiicient when clearing the whole table and unlike delete will reset any seed value as well!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 8

Ninja's_RGR'us (4/28/2010)
Grant Fritchey (4/28/2010)
I can't tell you specifically why truncate is allowed and not delete when you give the alter permission, but you're wrong about rollbacks. Truncate can be rolled back.
This could be attributed to granulity. Give the least amount of permissions to any user. You may need to be able to truncate a table to reload it from another environement. But without being allowed to delete 1 single row because you need to have both tables perfectly identical.

Yeah, it makes a sort of sense. It is much more of an administrative task, when looked at right, and much less about manipulating data. Partly because it's so complete.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning