Re: Executing dynamic select statement in a SP

From: John Bell (jrm_bell@hotmail.com)
Date: 12/30/02 

From: "John Bell" <jrm_bell@hotmail.com>
Date: Mon, 30 Dec 2002 14:36:09 -0000

Hi

>From the Execute topic in books online:

Permissions
EXECUTE permissions for a stored procedure default to the owner of the
stored procedure, who can transfer them to other users. Permissions to use
the statement(s) within the EXECUTE string are checked at the time EXECUTE
is encountered, even if the EXECUTE statement is included within a stored
procedure. When a stored procedure is run that executes a string,
permissions are checked in the context of the user who executes the
procedure, not in the context of the user who created the procedure.
However, if a user owns two stored procedures in which the first procedure
calls the second, then EXECUTE permission checking is not performed for the
second stored procedure

John

"Chen" <cshn@yahoo.com> wrote in message
news:030201c2b00c$1324cc30$89f82ecf@TK2MSFTNGXA01...
> Hi all,
>
> I have a SP which has dynamic SQL statements. I have given
> the execute only permission to couple of users on this SP.
> These users do not have any permission on the tables
> referred inside the SP. If I do not use the dynamic SQLs
> everything works just fine but with dynamic SQLs when I
> execute them in the SP I get select permission denied to
> tables. This is urgent. Any help is appriciated.
>
> Thanks in advance
>
> Chen

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)
  • 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)
  • 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)
Quantcast