Stored Procedure and Table permissions

From: Robert Mooney (anonymous_at_discussions.microsoft.com)
Date: 06/03/04 

Date: Thu, 3 Jun 2004 05:51:47 -0700

I want to:

1. create a user whose only permissions are to run a
stored procedure.

2. the stored procedure reads specific data from a table -
 allowing the user read-access to the specific data.

I've created the user and the stored procedure. I've
given the user EXEC permission for the stored procedure.
But I find I must give the user SELECT permission to the
table in order for the stored procedure to work - thus
exposing the entire contents of the table. I'm executing
the stored procedure from VBA in Excel.

Is there a way to configure permissions so that the user
only has EXEC permission to the stored procedure but no
permissions to the table? In that way I can control what
information the user sees.

Bob Mooney

Relevant Pages

  • Re: Deny access to all users (including Administrator and DomainAd
    ... permissions on stored procedure override ... applictaion's user account permissions to the stored procedure which will ... Will try to deny access through the query for SQL Server 2005 and 2000. ... Remove everyone that you don't want rom an Administrator Groups ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... I didn't understand what Dan was asking me to do. ... Then I am able to create a stored procedure. ... system tables with select permissions. ... You ARE the special DBO user, and not the new user, which is why you have ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... I logged on to the database through QA as dantest. ... "Dan Guzman" wrote: ... Windows group and thereby get sysadmin permissions. ... Then I am able to create a stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... Who is the owner of the database? ... "Dan Guzman" wrote: ... Windows group and thereby get sysadmin permissions. ... Then I am able to create a stored procedure. ...
    (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)