Re: Sql Security ?QL

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 11/17/04 

Date: Wed, 17 Nov 2004 07:07:41 -0600

To add to Uri's response, this behavior is called ownership chaining.
Permissions on indirectly referenced objects are not checked as long as the
same user owns all of the objects involved. However, dynamic SQL always
breaks the ownership chain and requires that the user have permissions on
objects referenced in the dynamically executed string. Below is an excerpt
from the SQL 2000 Books Online:

<Excerpt href="tsqlref.chm::/ts_ea-ez_05ro.htm">
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.
</Excerpt>

See http://www.sommarskog.se/dynamic_sql.html for other dynamic SQL
considerations. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"WJ" <JohnWebbs@HotMail.Com> wrote in message 
news:%23BAwtoFzEHA.2600@TK2MSFTNGP09.phx.gbl...
>I have the following two procedures:
>
> 1. Create Proc test1 @EmpNo Integer as
>          select * from Employees where EmployeeID=@iEyeNo
>    go
>
> 2. Create Proc test2 @EmpNo Integer as
>         declare @sql varchar(80)
>         set @sql='select * from Employees where EmployeeID='+@EmpNo
>
>        EXEC @sql
>    go
>
> ** I granted "Execute" privilege on both procedures above to a Windows 
> 2003 account called "NetWork Service". This account is used by Asp.Net 
> applications to access MS/SQL Server 2000.
>
> The 1st proc works fine, however, the 2nd proc requires me to also Grant 
> "Select" privilege on TABLE "Employees" to the "NetWork Service" account.
> Why is that ?  I want to use the 2nd Proc because, in certain 
> circumstances, the EXEC will let me build dynamic query.
>
> Thanks,
>
> John
>
>
>
>

Relevant Pages

  • Re: EXEC Permission
    ... DBA should grant you 'EXECUTE' permissions on the object ... Also,If you use dynamic sql within the stored procedure it will be forced ...
    (microsoft.public.sqlserver.programming)
  • Re: Permissions with sp
    ... Users do not need any permissions on tables used in a stored procedure as ... you do not use dynamic SQL ... I programmed a Sp, which I gave permissions to some users to execute, ...
    (microsoft.public.sqlserver.security)
  • Solaris 10 autofs directory permissions - Solution
    ... the fact that my map file has 755 permissions not 644. ... If the execute permission is set, it becomes an executable map which is ... map is expected to return the content of an automounter map ...
    (SunManagers)
  • Re: Rights to execute jobs?
    ... Does anyone know if the rules surrounding stored procedure permissions apply ... Does this apply to a proc calling sp_start_job so that I ... raise the alert, so it could be anyone on the server. ... is that a group needs to not be an admin but still be able to execute ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie
    ... I will now tell you some basic commands. ... Easy file has rights and a owner. ... file and who may execute it. ... The last three characters are about the permissions of user ...
    (alt.linux)