kk.86manu (10/25/2012)

---

Iam just looking to restrict a login only to read from the tables but while executing the stored prcodure it must allow the user to do the above operations.Is this possible?

The user has permissions to execute the stored procedure.The stored procdure is having CREATE,ALTER TABLE, ALTER PARTITIONS statements in it?

kk.86manu (10/25/2012)

---

Thanks for your reply


Unfortunately 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.

create proc test1
WITH EXECUTE AS OWNER
as
begin
create table test1
(id int)
end 

WITH EXECUTE AS 'UserName'