truncate a table from sql user

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

truncate a table from sql user

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-12-13 : 05:21:56

I have database in which i have given a new sql user following permission
GRANT SELECT, UPDATE, INSERT, DELETE, EXECUTE on SCHEMA::DBO TO [mytestuser]

I have to truncate one of my table.I am trying it from the following stored procedure

ALTER PROCEDURE [dbo].[usp_EmptyAddressFile]

AS
declare @sql_string varchar(4000)
BEGIN

select @sql_string = 'truncate table ' +'tblMyTableForTruncation'
execute (@sql_string)

END

But i am getting error
Cannot find the object "tblMyTableForTruncation" because it does not exist or you do not have permissions.

If i run same stored procedure via SA user then no problem.
Please let me know how to overcome this problem.

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-13 : 05:31:33

For using Truncate, you need dbo permission.

From msdn:

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

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-12-13 : 05:46:11

Can you help me in EXECUTE AS clause in my condition

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-13 : 06:04:08

Have a look at:
http://msdn.microsoft.com/en-us/library/ms190384(v=SQL.90).aspx

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-12-13 : 06:16:51

Have change my stored procedure from

CREATE PROCEDURE usp_EmptyAddressFile
WITH EXECUTE AS SELF
AS TRUNCATE TABLE tblMyTableForTruncation;

It looks like working but have to check it with different combinations
Thanks a lot pk_bohra

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-13 : 06:28:26

It should work as long as user have permission to execute the SP.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources