Re: Security within a stored procedure

From: Mary Chipman (mchip_at_online.microsoft.com)
Date: 08/18/04 

Date: Wed, 18 Aug 2004 10:47:58 -0400

Are you creating/dropping temp tables in tempdb or actual tables in
the database? Are you running dynamic SQL inside the sproc? In general
you only need to grant EXEC on stored procedures and can deny
permissions on base tables as long as the code in the stored procedure
follows the rules. See "Programming Stored Procedures" and "Using
Ownership Chains" in SQL BOL for more info. You can download the
latest version of BOL from
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp.

--Mary

On Tue, 17 Aug 2004 17:19:14 -0700, "Demian Valle"
<dvalle@nmccentral.com> wrote:

>I have a user with a database role of "denydatawriter" so
>that the user can run reports in Access but not change any
>data in the SQL Server database.
>
>However, there is a stored procedure that this user needs
>to execute and the stored procedure creates and drops
>several temporary tables. I have granted the user EXEC
>permission on the stored procedure but SQL still insists
>that the user does not have permission to create or drop
>tables.
>
>I have also tried to temporarily grant permission to
>create and drop tables within the body of the stored
>procedure but this returns errors stating that the user
>does not have the ability to grant permissions.
>
>Is there a quick way to give a user the ability to do
>anything within the confines of a stored procedure but
>still maintain read-only access for the rest of the
>database?
>
>Thanks.

Relevant Pages

  • Re: Creating a database
    ... Please do yourself a favor, find a local user group, or buy a beginning book on SQL Server or take a class, you will save yourself a great deal of pain in the long run. ... CREATE DATABASE Products ... I can create a stored procedure from within the Server Explorer and from ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Help with SQL 2005 and Sourcesafe 6
    ... I understand where you're coming from but if you open up the .sql script ... stored procedure in the database that it belongs to. ...
    (microsoft.public.sqlserver.tools)
  • Re: Execute stored procedure only
    ... right to execute stored procedure in database called "mydatabase". ... deny on every permission and checked grant on execute. ...
    (microsoft.public.sqlserver.security)
  • RE: Security context of stored procedure
    ... Suppose there are three users in a database TEST_CHAIN, ... simple stored procedure: ... also grant it to test_sp2 ... SELECT permission denied on object 'authors', database 'test_chain', owner ...
    (microsoft.public.sqlserver.security)
  • Re: Execute stored procedure only
    ... You need CONNECT capability to be able to get into the database and execute ... You can compare this to having permission to eat a piece of pie, ... Should I not be able to just execute SP through QA? ... right to execute stored procedure in database called "mydatabase". ...
    (microsoft.public.sqlserver.security)