Re: Dynamic SQL in Stored Proc
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 10/18/05
- Next message: Joseph Bittman MVP MCSD: "Re: sa loginname being hacked"
- Previous message: LEO: "User_name on 229 error alert"
- In reply to: Will: "Dynamic SQL in Stored Proc"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 18 Oct 2005 12:09:41 -0400
It sounds like the user was granted access to the underlying tables.
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com . "Will" <WillCWirtz@Yahoo.com> wrote in message news:1129649346.104637.10300@g43g2000cwa.googlegroups.com... 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.
- Next message: Joseph Bittman MVP MCSD: "Re: sa loginname being hacked"
- Previous message: LEO: "User_name on 229 error alert"
- In reply to: Will: "Dynamic SQL in Stored Proc"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
