Re: Sql Server - Basic Permissions Question


"Ranginald" <davidwank@xxxxxxxxx> wrote in message
news:1156718450.101845.48600@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a SQL Server database and I want a user to be able to execute
stored procedures only. These stored procedures use SELECT and WHERE
statements and return integers.

I have set EXECUTE permissions to the sp's as needed for the user.

It is possible to allow a user to have EXECUTE permissions on the sp's,
but deny everything else? E.g. If id did denyreader and allowed
EXECUTE on the sp's, would the command fail because the sp's have
SELECT and WHERE statements?


Basically this will work.

Look up "ownership chains" in BOL. Whan a user is granted EXECUTE on a
stored procedure, SELECT (and other) permissions checks are supressed
durring the execution of the stored procedure so long as the owner of the
target table is the same as the owner of the stored procedure.

David


.

Relevant Pages

  • 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)
  • Re: using EXECUTE AS for stored procedures, sql 2005
    ... Each stored procedure has one, and exactly one account that is ... EXECUTE AS OWNER. ... execute from the security context of said owner. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQLserver and the WHERE x IN y
    ... to a stored procedure - that would only work if you introduced subtyping into ... the temp table from the calling procedure (the temp table is still in scope, ... INSERT INTO #MyTempTable EXECUTE YourPopulateProc 123 ... INSERT INTO @MyTableVar VALUES ...
    (microsoft.public.sqlserver.programming)
  • RE: SQL stored procedure executing twice
    ... caused the stored procedure to execute twice. ... from one parent record to another. ... Dim stDocName As String ... The table tempCount does not change until I execute the VBA code line: ...
    (microsoft.public.access.modulesdaovba)
  • Re: pass stored procedure parameters in asp
    ... you can use the EXECUTE AS statement to test the ... procedure and properties) of voidTran stored procedure and other ... This email account is my spam trap ...
    (microsoft.public.inetserver.asp.general)