Stuck with Truncate

[wquatan]

Hi,

I have a SQL Server 2005, used in combination with Access VBA.
The SQL Server uses Windows Authenticatiob

I want to do "Truncate Table" from within VBA.

At first I wrote the Truncate in VBA (ExecuteSPT), which works fine to me (owner of the SQLserver Database/Table). However running the code on another PC it fails. It's because of the rights, making that user Owner solves the problem, but this ain't an option.

Can this be solved or is it mandatory to go for PlanB ?

PlanB: Because I read somewhere, Stored procedures can be assigned to users, I wrote the Truncate stuff as a stored procedure, which I can execute (Parse) succesfully, but can't find a way to save it as a Stored procedure on the server. Despite I started with "New Stored procedure", the save option always results in a local save on my Desktop, not on the server

[HTML]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [TruncateTable]
@TableName VARCHAR(50)
AS
BEGIN
TRUNCATE TABLE [dbo].[@TableName]
END
GO
[/HTML]

Even If the save would be ok I'll still have to find out how to allow the Stored Procedure for an user, and how to launch it from VBA.

I've been Googling / Reading, but can't find one "complete" example as a good (noob-understandable) example

Someone any ideas ?

[ck9663]

You don't save stored procedure as file like other development/data base tool that you can call.

Open a query analyzer and execute your script. The problem with this is you have to create one stored proc for every user, too messy.

You might need to give execute rights for those users and call the stored proc using the full naming convention db.dbo.sp

-- CK