Re: Stored procedure to drop and create table for end user

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 06/09/04 

Date: Wed, 9 Jun 2004 21:44:09 +0200

> I am using stored procedures in my application to keep the end users from
> having permission on the database that they should not (like delete rows and
> such) so the only thing the end user has access to is execute permission on
> the stored procedures... BUT the problem is it sometimes (not always) comes
> back with select permission not granted on a table the stored procedure is
> trying to access...

Probably because you are using dynamic SQL. Try to avoid it.

>anyways, we need to give a single stored
> procedure drop and create table access... which we don't want to grant it
> wide open across the user base that would be bad... but a temp table needs
> to be created and droped when its done with in the stored procedure...

All users can create temp tables. No need to grant anything for that. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Henry" <brianiupmsdn@newsgroups.nospam> wrote in message
news:%238BHAllTEHA.3016@tk2msftngp13.phx.gbl...
> Hi,
>
> I am using stored procedures in my application to keep the end users from
> having permission on the database that they should not (like delete rows and
> such) so the only thing the end user has access to is execute permission on
> the stored procedures... BUT the problem is it sometimes (not always) comes
> back with select permission not granted on a table the stored procedure is
> trying to access... so i had to go through and give select access to the
> tables (which i dont like doing considering some of them have sensative data
> in them) i thought a stored procedure is independent of end users table
> permissions? as long as they had execute permissions only the data returned
> is what they should get... regardless of the table permissions (thought
> stored proces ran as DBO or SA...) anyways, we need to give a single stored
> procedure drop and create table access... which we dont want to grant it
> wide open across the user base that would be bad... but a temp table needs
> to be created and droped when its done with in the stored procedure... well
> right now we get permission errors on it when this happens... what can i do
> about this?! i dont want to give everyone SA access....
>
>

Relevant Pages

  • 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: 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)
  • Re: Security context of stored procedure
    ... It seems that cross database tables are completely blocked from stored ... > grant exec ... > simple stored procedure: ... SQL Server will not check the permission of the ...
    (microsoft.public.sqlserver.security)
  • Re: EXECUTE permission denied on object... uh?
    ... connecting with doesn't have permission to execute the Stored Procedure ... runBehavior, SqlCommand cmdHandler, SqlDataReader ...
    (microsoft.public.dotnet.framework.aspnet)
  • Stored procedure to drop and create table for end user
    ... such) so the only thing the end user has access to is execute permission on ... back with select permission not granted on a table the stored procedure is ... tables (which i dont like doing considering some of them have sensative data ...
    (microsoft.public.sqlserver.programming)