August 8, 2008 at 8:23 am
Hello all,
I am stuck on this one - I want to allow a user access to run stored procedures for reporting purposes without allowing any select access to the underlying tables. My problem is that some of these stored procedures use "sp_executesql" and as a result, return an error message such as:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'tester', database 'myTest', schema 'dbo'.
I know that I can embed "EXECUTE AS" into the stored procedure just prior to the sp_executesql statement, but I am concerned about the impersonation. If I allow an account impersonation for this purpose it may be used to access the tables directly anyway. Is there a way to restrict this?
Some sample code for setting up the scenario:
create table [dbo].[Tester]
(
MyOnlyColumn varchar(20)
)
insert [dbo].[Tester]
select 'My Only Row'
create procedure [dbo].[Test1] as
begin
select * from [dbo].[Tester]
end
create procedure [dbo].[Test2] as
begin
exec sp_executesql N'select * from [dbo].[Tester]', N''
end
grant execute on [dbo].[Test1] to myTestUser
grant execute on [dbo].[Test2] to myTestUser
exec [dbo].[Test1]
exec [dbo].[Test2]
Regards,
Michael Lato
August 8, 2008 at 8:37 am
If you create the procedure WITH EXECUTE AS, then you don't need to grant any impersonation permissions. Only the person creating the proc will need impersonation rights, not the person running it, and the impersonation will revert as soon as the proc ends.
Something like this:
CREATE PROCEDURE TheProc
WITH EXECUTE AS 'SomeUser'
-- Do stuff here
The other option, of course, if to see if the dynamic SQL can be removed, though, depending on what it's doing, that may not be possible
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2008 at 9:13 am
Hello Gail,
Thank you - that works like a charm. Obviously, I need to spend some more time in security class! If you are coming to PASS in Seattle this year I'll be sure to buy you a beer.
Regards,
Michael Lato
August 8, 2008 at 9:24 am
Michael Lato (8/8/2008)
If you are coming to PASS in Seattle this year I'll be sure to buy you a beer.
Make it coffee and you're on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply