April 22, 2009 at 6:02 am
when i say "execute as user = 'user1' " inside a stored procedure. whether execute permission alone on the 'user1' to the stored procedure is enough or i should give permission on 'user1' to all tables & views i.e.sql objects used inside the stored procedure to perform insert/update/delete operations.
This is based on the assumptions all sql objects have same ownership chaining.
create proc sp_test
(
@par1 varchar(100),
@par2 int=null
)
as
begin
execute as user = 'user1'
set nocount on
begin try
insert into table1
select col1, col2 from tbl1
delete from table2
where col3 = 3
end try
begin catch
return 1
end catch
return 0
end
go
Thanks
April 24, 2009 at 3:27 am
Plz check http://msdn.microsoft.com/en-us/library/ms191296.aspx.
The example in the link will explain all to you.
April 26, 2009 at 9:24 am
I will attempt to answer this question: I have gone thru the link posted in this thread and my answer is based on the understanding derived from that msdn article. People familiar with this topic may please correct me if my understanding is incorrect.
By specifiying an execution context (such as by using the execute as user = 'user1') in which a module (such as a sp, trigger, queue etc) is executed, we can control the 'user account' that SQL Server will use to validate permissions on any objects referenced by the module. Therefore, the answer to your question will be: You may have to provide permission to user1 depending on whether this user has access to the objects being referenced in your sp; if user1 has permissions then there is no problem but if user1 does not have permissions then you will have to provide those permissions.
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
April 26, 2009 at 10:28 pm
By specifiying an execution context (such as by using the execute as user = 'user1') in which a module (such as a sp, trigger, queue etc) is executed, we can control the 'user account' that SQL Server will use to validate permissions on any objects referenced by the module. Therefore, the answer to your question will be: You may have to provide permission to user1 depending on whether this user has access to the objects being referenced in your sp; if user1 has permissions then there is no problem but if user1 does not have permissions then you will have to provide those permissions.
I think the same thing is illustrated in the link http://msdn.microsoft.com/en-us/library/ms191296.aspx."> http://msdn.microsoft.com/en-us/library/ms191296.aspx.
The link was mentioned so that the OP can get the clear understanding and difference between EXECUTE AS USER ANd EXECUTE AS LOGIN.
Shortly, one can get benifit from the permissions of another user IF one has the Rights of Impersonate.
Now comming to the OP, I think you should Impersonate to the User having rights on the objects used in the SP (on which the Caller do not have rtights).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply