Permissions for executing a stored procedure

From: DeeJay Puar (deejaypuar_at_yahoo.com)
Date: 05/19/03 

Date: Mon, 19 May 2003 08:16:19 -0700

Hi,

Truncate table permissions only default to table owner,
members of sysadmin, db_owner, ddl_admin and CAN NOT be
transferred.

So, eventhough, you have granted execute permissions to
stored procedure, the user also needs to part of one of
the roles mentioned above.

Instead using TRUNCATE TABLE, you could use DELETE FROM,
which is slower and logged I believe.

hth

DeeJay
>-----Original Message-----
>We have a stored procedure that has code in it to
truncate tables. We do
>not want to grant DDL permissions to the user id that is
going to execute
>this stored procedure. From what I've read it seems like
I should be able
>to grant the user "execute" permissions on that specific
stored procedure,
>and even though the user does not have DDL permissions,
the store procedure
>should run successfully. But when the user runs the
procedure they are able
>to execute it, but as soon as it tries to truncate the
table it craps out
>saying the user doesn't have the proper authority. Any
suggestions?
>Thanks!
>
>
>.
>

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: With in a SP Truncate dbo.table table-name permissions..
    ... TRUNCATE TABLE permissions default to the table owner, ... According to sql server ... If a user who creates a stored procedure does ...
    (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)
  • With in a SP Truncate dbo.table table-name permissions..
    ... If i give execute permission to this ... truncate table permissions on the table. ... If a user who creates a stored procedure does ... not qualify the name of the tables referenced in SELECT, ...
    (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)