SQL SERVER – Securing TRUNCATE Permissions in SQL Server

All data from everywhere here on Earth go through a series of  four distinct operations, identified by the words: CREATE, READ, UPDATE and DELETE, or simply, CRUD. Putting in Microsoft SQL Server terms, is the process goes like this: INSERT, SELECT, UPDATE and DELETE/TRUNCATE.

Quite a few interesting responses were received and evaluated live during the session. To summarize them, the most important similarity that came out was that both DELETE and TRUNCATE participate in transactions. The major differences (not all) that came out of the exercise were:

DELETE:

DELETE supports a WHERE clause
DELETE removes rows from a table, row-by-row
Because DELETE moves row-by-row, it acquires a row-level lock
Depending upon the recovery model of the database, DELETE is a fully-logged operation.
Because DELETE moves row-by-row, it can fire off triggers

TRUNCATE:

TRUNCATE does not support a WHERE clause
TRUNCATE works by directly removing the individual data pages of a table
TRUNCATE directly occupies a table-level lock.
(Because a lock is acquired, and because TRUNCATE can also participate in a transaction, it has to be a logged operation)
TRUNCATE is, therefore, a minimally-logged operation; again, this depends upon the recovery model of the database
Triggers are not fired when TRUNCATE is used (because individual row deletions are not logged)

Finally, Vinod popped the big homework question that must be critically analyzed:

“We know that we can restrict a DELETE operation to a particular user, but how can we restrict the TRUNCATE operation to a particular user?”

After returning home and having a nice cup of coffee, I noticed that my gray cells immediately started to work. Below was the result of my research.

As what is always said, the devil is in the details. Upon looking at the Permissions section for the TRUNCATE statement in Books On Line, the following jumps right out:

“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. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.“

Read more: Journey to SQL Authority with Pinal Dave

Posted via email from .NET Info