With in a SP Truncate dbo.table table-name permissions..

From: venkat (vgaddam@us.imshealth.com)
Date: 07/18/02 

From: "venkat" <vgaddam@us.imshealth.com>
Date: Thu, 18 Jul 2002 06:38:00 -0700

Hi,

I have procedure with in the procedure I there is a
Truncate dbo.table table-name. According to sql server
books on line. If i give execute permission to this
procedure to a user that user should be able to execute
this procedure with out any other permissions on the
table. But that&#8217;s not true, it gives error does not have a
truncate table permissions on the table. I know it works
fine if I give "db_ddladmin" permissions on that user.

But I cannot give this permission on the production
database to a normal user.

If anybody has the solution please let me know.

SQL Server Books on line:
Qualifying Names Inside Stored Procedures
Inside a stored procedure, object names used with
statements (for example, SELECT or INSERT) that are not
user-qualified default to the owner of the stored
procedure. If a user who creates a stored procedure does
not qualify the name of the tables referenced in SELECT,
INSERT, UPDATE, or DELETE statements within the stored
procedure, access to those tables through the stored
procedure is restricted by default to the creator of the
procedure.

Object names used with the statements ALTER TABLE, CREATE
TABLE, DROP TABLE, TRUNCATE TABLE, CREATE INDEX, DROP
INDEX, UPDATE STATISTICS, and DBCC must be qualified with
the name of the object owner if other users are to use of
the stored procedure. For example, Mary, who owns table
marytab, must qualify the name of her table when it is
used with one of these statements if she wants other users
to be able to execute the stored procedure in which the
table is used.

This rule is necessary because object names are resolved
when the stored procedure is run. If marytab is not
qualified and John tries to execute the procedure, SQL
Server looks for a table called marytab owned by John.

Thanks a millions
Venkat
gaddamv@hotmail.com
610 834 5241

Relevant Pages

  • Re: Logon failed for user ". but only for membership tables in same database that other request wor
    ... I was able to log on to computer b and execute the stored procedure. ... was no error after I corrected the permissions for the user. ... I can access the stored procedure but I can not use membership.validateuser. ... You may use the GRANT statement to grant the EXECUTE permission for a ...
    (microsoft.public.sqlserver.security)
  • Re: Executing dynamic select statement in a SP
    ... EXECUTE permissions for a stored procedure default to the owner of the ... the statementwithin the EXECUTE string are checked at the time EXECUTE ... > everything works just fine but with dynamic SQLs when I ...
    (microsoft.public.sqlserver.security)
  • Re: SET IDENTITY_INSERT Privileges?
    ... on the stored procedure to get around this. ... stored procedure and the user has execute permissions on the ... How do I grant permission to a SET Statement?? ...
    (microsoft.public.sqlserver.security)
  • Re: app role has exec permission but still cant execute sp
    ... that the stored procedure references in it's code. ... ADO connection sometimes opens a new connection to the server if the first ... > The stored procedure which I can't execute is below, ... >>> well as some users with no permissions. ...
    (microsoft.public.sqlserver.security)
  • RE: SQL stored procedure executing twice
    ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
    (microsoft.public.access.modulesdaovba)