Re: EXEC in stored procedure

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 10/26/04 

Date: Tue, 26 Oct 2004 13:43:53 -0400

> Is the EXEC command inside the SP run as the user? Why?

One reason I can think of is because dynamic SQL is a very serious security
concern, and if you allow any table name -- or any SQL command, for that
matter -- to run, just because the user can run the stored procedure, then a
user can wreak havoc on your system quite easily.

So, the engine verifies permission once it resolves the dynamic SQL...

A

Relevant Pages

  • Re: EXEC in stored procedure
    ... > Is the EXEC command inside the SP run as the user? ... One reason I can think of is because dynamic SQL is a very serious security ...
    (microsoft.public.sqlserver.programming)
  • Re: EXEC in stored procedure
    ... The reason for using dynamic SQL is that the SP will collect info about ... Then this is used by a Cursor to add data from the tables specified in #tbls ...
    (microsoft.public.sqlserver.programming)
  • Re: EXEC in stored procedure
    ... The reason for using dynamic SQL is that the SP will collect info about ... Then this is used by a Cursor to add data from the tables specified in #tbls ...
    (microsoft.public.sqlserver.security)
  • Re: Can this work - soft code SP table name in parameter?
    ... Dynamic SQL forces ... security benefit of stored procedures. ... cutting and pasting four SPs is not a good reason in my book. ...
    (microsoft.public.sqlserver.programming)
  • Re: Help me convince the dev manager. Please.
    ... SQL - not just in the fact that it's not precompiled, ... > dev managers insists that there must be another method out there to handle ... firmly believe that dynamic SQL is the best ... > to handle optional parameters. ...
    (microsoft.public.sqlserver.programming)