Re: EXEC in stored procedure

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

  • Next message: Todd Hazer: "Disallow administrator access" 
    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
    >
    >

  • Next message: Todd Hazer: "Disallow administrator access"

    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)
    • 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: Help me convince the dev manager. Please.
      ... SQL Server MVP ... firmly believe that dynamic SQL is the best ... >> to handle optional parameters. ... >> ensure that the SQL string is formed correctly. ...
      (microsoft.public.sqlserver.programming)
    • 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)