kk.86manu (10/25/2012)
Thanks for your replyUnfortunately i am still not able to do this.Please see the details below
Scenario:
I have a login test_login which has role as db_datareader and db_datawriter.
Created this stored procedure with another login which had full access.The stored procedure has create table statement inside it
create proc test1
as
begin
create table test1
(id int)
end
I granted execution rights for the following object for test_login
GRANT EXEC ON test1 TO test_login
When i execute this SP with test_login .i get the error 'CREATE TABLE permission denied in database'.
I want this SP to create the table.Is this possible in current security context?
Please correct me if iam wrong.
If the owner of the procedure has the rights to create table / issue DDL statements, then you could edit your procedure like so:
create proc test1
WITH EXECUTE AS OWNER
as
begin
create table test1
(id int)
end
Otherwise you could use a specific SQL User that has these rights:
WITH EXECUTE AS 'UserName'
Edit: Didn't realize Gail already answered, damn you browser refresh!
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding