Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string
From: Narayana Vyas Kondreddi (answer_me@hotmail.com)
Date: 07/09/02
- Next message: Russell Fields: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Previous message: Dave: "DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- In reply to: Dave: "DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Next in thread: Russell Fields: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Narayana Vyas Kondreddi" <answer_me@hotmail.com> Date: Tue, 9 Jul 2002 18:17:36 +0100
To be able to use dynamic SQL, the user must have direct SELECT permissions
on the underlying tables. You might want to read this informative article on
dynamic SQL for more information:
http://www.algonet.se/~sommar/dynamic_sql.html
-- HTH, Vyas, MVP (SQL Server) Check out my SQL Server website @ http://vyaskn.tripod.com/ "Dave" <davena@my-deja.com> wrote in message news:38ba16eb.0207090904.6427cf70@posting.google.com... > I am trying to prevent access to a SQL Server 2000 database from other > than our web app. Our users have a long history of linking tables > from Access and other such tools, but in this particular database > there is some legally confidential data, such as names of kids up for > adoption. > > I have a Windows group named CPS_ILP. I have a server group named the > same [qa-web-1\cps_ilp]. I wrote a script that GRANTS EXECUTE to > every stored procedure in the database to [qa-web-1\cps_ilp] and DENY > SELECT, INSERT, UPDATE, DELETE on all tables TO [qa-web-1\cps_ilp]. > So members of the cps_ilp global group are able to run our web app > which executes stored procs but are not able to get anything with > query analyzer or Access. The trouble we are having is that the > programmer EXECUTEs a string dynamically to get data out of tables, > like so: > > SET @Str = 'SELECT Name, Address, Phone FROM tblHousing' > EXECUTE @Str > > Access is denied because SQL Server thinks we are SELECTing directly. > There is a statement somewhere earlier on usenet that says that if we > were using static statements in our stored procs instead of EXECUTE, > we wouldn't be having this problem. It doesn't say why or give a > workaround. We really need to be able to EXECUTE like we are now. > Any ideas how we can accomplish this? Thanks, Dave
- Next message: Russell Fields: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Previous message: Dave: "DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- In reply to: Dave: "DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Next in thread: Russell Fields: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
