Re: Executing dynamic select statement in a SP

From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 12/30/02 

From: "Dan Guzman" <danguzman@nospam-earthlink.net>
Date: Mon, 30 Dec 2002 08:30:41 -0600

Dynamic SQL is always executed in the security context of the executing
user. You need to use static SQL if you need to leverage ownership
chains.

Check out Erland's article on dynamic SQL considerations
<http://www.algonet.se/~sommar/dynamic_sql.html> 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy  Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"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: Exec permissions on Stored Procedure
    ... they have Select permissions on a table that the Stored Procedure uses? ... execute permission on a stored procedure, that's all that needs to be ... requiring me to also grant select permission to the underlying table. ... does not apply to dynamic SQL, because dynamic SQL is not part of the ...
    (microsoft.public.sqlserver.security)
  • Re: SP_EXECUTESQL Security in 2005
    ... the Impersonating User only has access to run a SP, ... occasions) create Dynamic SQL ... security to all SP's so that the impersonating user only has access to ... Execute the SP's. ...
    (microsoft.public.sqlserver.security)
  • Re: SP_EXECUTESQL Security in 2005
    ... the Impersonating User only has access to run a SP, ... occasions) create Dynamic SQL ... security to all SP's so that the impersonating user only has access to ... Execute the SP's. ...
    (microsoft.public.sqlserver.security)
  • Re: Dynamic SQL to call a function with RETURN value?
    ... 'DISASTER' inscribed all over it. ... Could you point to Oracle documentation, where it lists Dynamic SQL ... str_func will be set in a cursor Loop using function calls ... EXECUTE IMMEDIATE cStmt USING OUT nReturnCode; ...
    (comp.databases.oracle.misc)
  • Re: Custom sort records in a stored proc
    ... based on parameter, you execute the respective> select statement. ... pass tablename, columnnames as parameters - and execute them using> dynamic sql. ... >> Is there a way to pass a parameter to the proc that represents a>> specific primary key value and have the corresponsding record appear>> first in the result set? ...
    (microsoft.public.sqlserver.programming)