Post reply

sp_ExecuteSql permission chaining

  • August 11, 2004 at 7:55 pm

    #86281

    I am new to this site so please forgive me if this is in the wrong section or stupid question.

    Problem: I am using sp_ExecuteSql to run dynamic sql statements from within a stored procedure. I know this goes against a lot of advice but I have not found a better way to allow the user to have control over the select columns returned as well as the where criteria. Limiting the columns returned can reduce the network traffic significantly in my opinion.

    So assuming I am stuck with dynamic sql, I would like to lock down access to my tables, and only give access via sprocs. But of course using sp_Executesql prevents this. Is there any way around this whatsoever?

    Any help would be greatly appreciated.

    TryHard

  • August 12, 2004 at 8:16 am

    #518680

    No, not in SQL Server 2000 (there is help on the way in SQL Server 2005). When you run dynamic SQL in SQL Server 2000, it executes that code in a separate batch from the first. That means new security checks and ownership chaining is not continued from the calling stored procedure.

    You could create a view to restrict the columns accessible if a user doesn't need to see all the columns on a table and then give access to the view. While column-level permission is very viable, the view makes the security measure a little more obvious to the DBA and others who might look into it later.

    K. Brian Kelley
    @kbriankelley

  • August 12, 2004 at 4:16 pm

    #518778

    Thanks for your reply.

    Unfortunately views are really great options because there are many diferent areas of the business interested in a variety of data. Its not really about security of the data in the columns, more only returning the required info over the nextwork.

    Thanks again, I will have to put more thought into this.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply