Re: EXEC in stored procedure

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Magnus Blomberg (magnus.blomberg_at_skanska.se)
Date: 10/26/04 

Date: Wed, 27 Oct 2004 00:09:09 +0200

Hi all of you!

Well, I thought I should get that question. This message is written at home
without any SQL, so it might be small code errors.
First I can tell you, this SP doesn't accept any input parameters, so I
think the security riscs are quite small.
I must also say, that the system is rather old and is using application
role, so the objects aren't made for any user access for the users.

The reason for using dynamic SQL is that the SP will collect info about
tables into table #tbls as TableName, IDColumn, ValueColumn etc.
Then this is used by a Cursor to add data from the tables specified in #tbls
to table #rows.
    Eg: INSERT #tbl (@IDColumn,@ValueColumn) INTO #rows FROM @TableName
    Correct me if I'm wrong but the row above is not valid, because it's not
using dynamic SQL, yea?!?!
Then the SP should return as SELECT * FROM #rows

This is the complete purpose, and I found this very difficult not using
EXEC...

Any ideas how to do the similar without using dynamic SQL.

Regards Magnus

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OgfAhO4uEHA.3456@TK2MSFTNGP14.phx.gbl...
> > 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: 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)
  • Preaching does not help
    ... Problems with scalar function, and dynamic SQL ... Select ReturnMe from #temp ...
    (microsoft.public.sqlserver.programming)
  • Re: Sending NULL value to parameter of Stored Procedure
    ... Looking for a SQL Server replication book? ... With that number of options dynamic SQL is the best option, ... "Hilary Cotter" wrote: ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Sending NULL value to parameter of Stored Procedure
    ... With that number of options dynamic SQL is the best option, you need to understand the implications, SQL injection, multiple plans. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Sending NULL value to parameter of Stored Procedure
    ... With that number of options dynamic SQL is the best option, ... Have a read of Erlands Dynamic sql article http://www.sommarskog.se/dynamic_sql.html ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.fulltext)