Dynamic SQL in Stored Proc

From: Will (WillCWirtz_at_Yahoo.com)
Date: 10/18/05 

Date: 18 Oct 2005 08:29:06 -0700

Environment:
Window2K workstation, SQL Server 2000 Vesion 8.00.760 (SP3)

Setup:
I have a database setup so that NO users (except dbo) have READ,
UPDATE, or DELETE access to my database. But I have a single role
called MySPUser that is granted EXECUTE access to all of my stored
procs that do all data access for the system. The MySPUser role has a
single user in that group called MyUser, which is a windows domain
level account. My Webserver then impersonates that user when it calls
the stored procs. This setup worked on both my development machine and
my development test machine.

Problem:
So everything was going great for about a year when my dev machine
crashed. When I rebuilt the box with the same software (os and sql
included) everything seemed to be working just fine. The impersonated
user can still call all the stored proc and either retrieve or update
data. The only problem is that I have 2 stored proc that require
Dynamic SQL and they have stopped working. I now receive the following
error message when executing one of the stored procs.

SELECT permission denied on object 'tblMyTable', database
'MyApplication-Dev', owner 'dbo'.

I have tried deleting the users from the database and server and fully
rebuilding the users and roles with no luck. If I change my connection
string to point to my Test machine, which was built a year ago and also
uses Win2K and SQL2K SP3 everything seems to work fine.

Question:
What could cause Dynamic SQL Stored Procs to execute under a different
security context than Non-Dynamic SQL Stored Procs?

Any help would be greatly appreciated.

Will

P.S. I need to user dynamic sql because the sql statement is a query
for data by the user that can be searched on 12 different fields
simultaniously. Therefore the number of combinations of statements I
would need to build would be huge.

Relevant Pages

  • Re: Help me convince the dev manager. Please.
    ... The query cost w/ dynamic SQL won't necessarily be lower. ... Some might complain about the maintaining multiple stored procs, ... > is no database design or structure that would help with this problem. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Injection Prevention
    ... I was pointing that you're not considering it in isolation but with the ... I think it's much more likely that an application developer would build the ... sql string from input that the database developer would do it in dynamic sql. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... I was pointing that you're not considering it in isolation but with the ... I think it's much more likely that an application developer would build the ... sql string from input that the database developer would do it in dynamic sql. ...
    (microsoft.public.dotnet.security)
  • Re: Confused about proc vs. dynamic SQL vs LINQ
    ... where people create stored procs which *just* call normal CRUD ... for no reasons other than dogma. ... theoretical "change the database and the procs implementation, ... It depends on the database server, but I believe that using a cached ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: ASP coding help required please
    ... Your database will not know anything about the Request object. ... sType = Request.Form ... sAcct = Request.Form ... Avoid the dynamic sql approach, but if you are determined to use it, see: ...
    (microsoft.public.inetserver.asp.db)