If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Execute privileges on sp

Hi. I'm trying to test something on a test db I have installed on my pc, but I am unable to process as I'm doing it. So, basically what I want is to give execute privilege on a procedure to a user, so the user can execute this procedure without having the privileges explicity granted on it (what this procedure do is to truncate a table on which the user has no access). As I've read, SQL Server stored procedures privileges runs with the definers permissions, not the one that is actually executing the procedure. So, what I'm doing is this: in query analyzer, logged in as sa, I did

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.

I guess this mean I cannot transfer TRUNCATE TABLE privilege to a user, but since I am executing a procedure I created on my own ( as sa in this case ), woulnd't it have to run with my privileges instead of the user who is executing it ( in this case, jmartinez ) ?

Hmm.. TRUNCATE TABLE is an operation that requires high priviledges as long as I know, so you will have to give a user them.

By the way, why does user need to perform this? IMHO, You may create something like and administration panel which will login to database on its own and perform TRUNCATE TABLE instead of user - e.g. he selects a table from the combo box and then presses SUBMIT button, and then script connects as someone special with appropriate role membership and runs TRUNCATE TABLE.