Permissions

Question

Post reply

Permissions

sqlguru

SSC-Addicted

Points: 471
More actions
April 22, 2021 at 10:51 pm

#3874560

I am looking for option to truncate table since we don't need the logs of what deleted.
Below is what I found with minimum level of access, Grant control on table to user. Do you agree or is there a better way?
https://www.sqlservercentral.com/blogs/sql-server-truncate-table-permissions

Viewing 7 posts - 1 through 6 (of 6 total)

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

frederico_fonseca SSCoach Points: 16014 More actions · Answer 1

no. not the best way, neither the minimum access - its more like FULL access.

grant control on a table is a SUPER SET of permissions that include ALTER (which is enough for the truncate bit)

CONTROL grants the following permissions on the object and these are WAY too much to grant

ALTER
CONTROL
DELETE
INSERT
REFERENCES
SELECT
TAKE OWNERSHIP
UPDATE
VIEW CHANGE TRACKING
VIEW DEFINITION

This reply was modified 3 years ago by frederico_fonseca.

sqlguru SSC-Addicted Points: 471 More actions · Answer 2

Thanks. Can you advise the better option for truncate only table level permission? This particular table will be called in store proc.

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

Just ALTER on the table is the minimum permission for TRUNCATE

frederico_fonseca SSCoach Points: 16014 More actions · Answer 4

either ALTER table or execute permissions on a proc that does the truncate like the following

create procedure TruncateMySchemaTable
with execute as owner
as truncate table schemaname.MyTable;

sqlguru SSC-Addicted Points: 471 More actions · Answer 5

Thanks. If execute as clause is used means then the store proc's need to be modified right?

frederico_fonseca SSCoach Points: 16014 More actions · Answer 6

the truncate sp needs execute as owner.

the sp that calls it does not need to have "execute as owner" - only change you need to do on that one is to replace "truncata table" with "exec truncatetablexsp"