Re: Executing dynamic select statement in a SP

From: Sue Hoegemeier (Sue_H@nomail.please)
Date: 12/30/02 

From: Sue Hoegemeier <Sue_H@nomail.please>
Date: Mon, 30 Dec 2002 07:24:37 -0700

If the stored procedure uses dynamic SQL then the users will
need permissions on the objects referenced in the SQL
statement. That's just the way it works.
You can find an interesting article on dynamic SQL at:
http://www.algonet.se/~sommar/dynamic_sql.html

-Sue

On Mon, 30 Dec 2002 06:02:22 -0800, "Chen" <cshn@yahoo.com>
wrote:

>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: Logging within User_Defined Functions (UDF)
    ... You can't perform dynamic SQL or DML in a function. ... Looks like a stored procedure that you ... Ultimately you can only execute ... > DECLARE @cmdstr nvarchar ...
    (microsoft.public.sqlserver.programming)
  • Re: stored procedure security
    ... > execute privelages on the stored procedure, ... As soon as you employ dynamic sql, the logon executing the stored procedure ... In order to execute this stored ...
    (microsoft.public.sqlserver.security)
  • Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string
    ... Once you go to dynamic SQL, you are outside the ownership / security context ... I wrote a script that GRANTS EXECUTE to ... every stored procedure in the database to and DENY ...
    (microsoft.public.sqlserver.security)
  • Re: Confused with security
    ... >Using windows integrated security. ... Your guess is correct - welcome to the pitfalls of dynamic SQL. ... If a stored procedure references a table owned by the same userid that ... Each user that has rights to execute the procedure can ...
    (microsoft.public.sqlserver.server)
  • Re: Confused with security
    ... >>Using windows integrated security. ... > Your guess is correct - welcome to the pitfalls of dynamic SQL. ... > owns the stored procedure, no additional check for access right to that ... Each user that has rights to execute the procedure can ...
    (microsoft.public.sqlserver.server)