Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-08 : 15:08:15
|
Nathan writes "I'll explain the scenario.
UserA has Exec permission on stored procedures sp_ExampleA and sp_ExampleB, and NO permissions on table tbl_ExampleTable.
sp_ExampleA will work fine because the stored procedure will run the SELECT statement as dbo.
sp_ExampleB will not work because an EXECUTE statement will only run as the User running it (not dbo, but in this case UserA - and UserA doesn't have permission on tbl_ExampleTable).
Sure, I know that building and executing strings is a bad thing from the point of view of the optimizer, readability, maintainability, but it is excellent for debugging, and having a stored procedure with optional parameters building a complex WHERE clause is excellent to abstract the intricacies of the database schema from developers.
My question is: Is there anything I can do about this? I don't want to give UserA permission to tbl_ExampleTable and I want to keep using EXECUTE.
CREATE PROCEDURE dbo.sp_ExampleA
AS
SELECT * FROM tbl_ExampleTable
GO
----------------------------------------------------------
CREATE PROCEDURE dbo.sp_ExampleB
AS
DECLARE mystring nvarchar(4000)
SET mystring = 'SELECT * FROM tbl_ExampleTable'
EXECUTE(mystring)
GO
Regards
Nathan Rogers" |
|
|
|
|
|
Using EXECUTE(somestring) within security context of a stored procedure