Re: app role has exec permission but still can't execute sp

From: Adi Cohn (adicohn@hotmail.com)
Date: 11/07/02 

From: "Adi Cohn" <adicohn@hotmail.com>
Date: Thu, 7 Nov 2002 16:56:52 +0200

  I was talking about extended stored procedure such as xp_cmdshell, sp_oa*
and some other, but from your code, I can see that this is not relevant.
Another thing that might be relevant is ownership chain. You don't have to
give permissions on the base objects (in your case tables tblProlongatie and
tblBoeking) only if the owner of the stored procedure also owns the objects
that the stored procedure references in it's code.

  ADO connection sometimes opens a new connection to the server if the first
connection is busy. Maybe this is what is happening to you. The second
connection will be opened with the same credentials that were used for the
first one. In order to prevent it, you need to specify it in the
connection string (I think that you have to specify pooling=false, but I'm
not sure about it)

Adi

  One more thing that I remember reading (don't remember where), is that ADO
sometiems opens another "Diederik" <diederikvanpeer@hotmail.com> wrote in
message news:9cd501c2865e$8fbe5ae0$3aef2ecf@TKMSFTNGXA09...
> Thanks for your reply, but could you please be more
> specific? Which extended stored proc's are you talking
> about?
> The stored procedure which I can't execute is below, as
> you can see, nothing special is done inside it:
>
> CREATE PROCEDURE sp_TotaalProlongaties
> @rec_id int
> , @premie money output
> , @provisie money output
> AS
> SELECT @premie = SUM(COALESCE
> (prl.prl_brutopremieD,0) - COALESCE
> (prl.prl_brutopremieC,0))
> + SUM(COALESCE
> (prl.prl_ass_belD,0) - COALESCE(prl.prl_ass_belC,0))
> , @provisie = SUM(COALESCE
> (prl.prl_doorl_provD,0) - COALESCE(prl.prl_doorl_provC,0))
> FROM tblProlongatie prl
> INNER JOIN tblBoeking bkn ON
> prl.prl_bkn_id = bkn.bkn_id
> WHERE bkn.bkn_rec_id = @rec_id
>
> >-----Original Message-----
> > If you are using dynamic SQL in the stored procedure,
> then you need to
> >grant permission on base objects as well as on the stored
> procedure. Also
> >there are some extended stored procedure that you need to
> assign permission
> >on even if they are being called from a stored procedure.
> >
> >
> >"Diederik" <diederikvanpeer@hotmail.com> wrote in message
> >news:960801c28647$0c7fd760$2ae2c90a@phx.gbl...
> >> Hi all,
> >> I've set up an application role in my SQL2K database as
> >> well as some users with no permissions. All permissions
> >> (including execute permissions) are granted to the
> >> application role.
> >> The users log on to the database through my Visual Basic
> >> application. After succesful logon, the security context
> >> is switched to the application role's
> via 'sp_setapprole'
> >> (called from within the VB application). So far so good.
> >> However, when I try to execute a stored procedure
> through
> >> the ADO command object, I get the 'EXECUTE permission
> >> denied on object...' message. I don't understand this,
> I'm
> >> positive that the application role has the execute
> >> permission for the stored procedure, and I'm also sure
> >> that the call to sp_setapprole went ok. Am I missing
> >> something here?
> >>
> >> Any help is appreciated.
> >>
> >> Diederik
> >
> >
> >.
> >

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: 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: ARITHABORT problem
    ... Anyway, when you open the AdoConnection, execute the following: ... Connection:= ... > that inserts new rows into the table, I get an ARITHABORT error. ... > the same stored procedure from the MS SQL QA it works fine. ...
    (borland.public.delphi.database.ado)
  • Re: ARITHABORT problem
    ... Anyway, when you open the AdoConnection, execute the following: ... Connection:= ... > that inserts new rows into the table, I get an ARITHABORT error. ... > the same stored procedure from the MS SQL QA it works fine. ...
    (borland.public.delphi.database.ado)
Quantcast