C# – Sql server – execute stored procedure with readonly permission

csql server

I have an application where a user can input a sql statement to be executed in an sql server database. The statements must only return data and not perform any DML or DDL operation. At the moment the input is validated only allowing to write SELECT statements. But now is a requirement allow the execution of stored procedures, but only those that only read data. Also would be helpful list these SP in order to show a kind of intellisense.

I thought in create a user with only db_datareader permission and execute the statement as this user. But to execute an stored procedure I must grant Execute permission to the user and becuase the Ownership Chaining, the permissions may not be checked. I haven't figured it out how to solve this problem.

Also I thought in allow only the execution of stored procedures createds in an specific DB schema that only has read permission. Also this way I can list the SPs easily. I don't know if this is possible.

Best Answer

But to execute an stored procedure I must grant Execute permission to the user

This should work by creating an user without login and granting only select to that login.This uses EXECUTE as clause..You need EXECUTE as permissions to create or call this module,but the execution context will be user1 ,once you are in the module which has only select permissions

create user user1 without login

grant select to user1

alter proc usp_test
@sql nvarchar(max)
with execute as 'user1'

as
begin


exec sp_Executesql @sql

end


create table dbo.test
(
id int
)

insert into test
select 1
go 10

exec usp_test N'select * from test' --works
exec usp_test N'insert into test values(1)' --fails with below error

Msg 229, Level 14, State 5, Line 28 The INSERT permission was denied on the object 'test', database 'master', schema 'dbo'.

Related Topic