Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-07-10 : 09:10:44
|
| Erik writes "I created a stored procedure that is truncating a table that it owns. I granted execute rights on this stored procedure to another user. When I execute this stored procedure as this other user, I get the following error:Cannot execute the SQL statement "{call hr.dbo.sp_truncate_personnel}". "(HY000/3704) [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]User does not have permission to perform this operation on table 'hr.dbo.personnel'."I thought that stored procedures executed with the owner's rights, not the invoker's rights. Is there anyway to truncate a table from a user that does not have special rights, only rights to execute a stored procedure that performs the truncate?" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-10 : 09:17:37
|
| From bolThe 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. For more information, see Using EXECUTE AS to Create Custom Permission Sets.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
stored procedure's rights to truncate a table