Truncate Table - Giving Permissions

[WillStanley]

I want to give non-administrators (general users) the ability to truncate tables via a stored procedure.

Is it possible without making them a member of a standard admin group such as db_ddladmin.

I presume from reading Books Online that you cannot create a new server role with only TRUNCATE as an allowed command.

Books Online States........
Permissions
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.

 

[TonyKhela]

Create a stored procedure that truncates the table.

Give EXECUTE permission on that Stored Proc to the users.

Ensure that the owner of the Stored Proc is the same as the table i.e. dbo.

Thats it.

Hope it helps.

TK

 

[WillStanley]

This definately helps me up to a point.

The reason being that the stored procedure is created on the fly by an access form. Therefore the form is owned by the user.

The only solution I can think of is to have a permanent Truncate sp (with owner dbo) that gets called from the sp created on the fly. It is a bit unelegant but I will try this.

If you have any other ideas I would love to hear them.

thx

will