3

Our SQL Server database has a reporting feature that allows callers to read, but not write, any table, because the user (or, more precise, the connection opened by the web app that's operating on behalf of the user) has only datareader permissions on the database.

We'd like to be able to write a store procedure that is a special "cleanup report" that will scrub the DB of old cached data before running another report. We'd like the same read-only user above to be able to run this stored proc. The queries inside the stored proc will do DELETE operations, but we don't want to give the user the ability to delete anything other than by via calling this proc.

I know about Module Signing but was hoping to avoid the complexity of dealing with certificates.

Is there another solution? We're using SQL Standard Authentication if that matters.

Justin Grant
  • 44,807
  • 15
  • 124
  • 208

2 Answers2

6
CREATE PROCEDURE dbo.my_procedure
WITH EXECUTE AS OWNER
AS
BEGIN
  -- do your stuff here
END
GO
GRANT EXEC ON dbo.my_procedure TO [your_datareader_member];
GO
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Actually, you don't even necessarily need the `WITH EXECUTE AS` bit (depends on what you are doing exactly), but you can [read about it here](http://msdn.microsoft.com/en-us/library/ms188354.aspx). – Aaron Bertrand Apr 12 '13 at 20:34
  • that MSDN doc says that "CALLER is the default for all modules except queues" so how is WITH EXECUTE AS OWNER optional? – Justin Grant Apr 12 '13 at 20:44
  • Like I said, it depends on what you are doing. To test this I created a login + a user in my database, added them to the db_datareader role only, then created a procedure that updated a table (and did not use any `EXECUTE AS` option. I granted the user the permission to exec that stored procedure, logged in as them, verified that I was not allowed to update the table directly, then ran the procedure, and the update occurred. You may need to approach things differently if you are going outside of the database, or trying to update tables where the user has an explicit deny, etc. Did you try it? – Aaron Bertrand Apr 12 '13 at 20:48
  • nope, haven't had a chance to try it yet-- got temporarily pulled off onto another project. Was just curious about default behavior. – Justin Grant Apr 13 '13 at 20:21
1

The granted permission to execute the procedure will allow the delete to occur.

In fact this is a very relevant scenario, to limit ability to perform certain operations (such as delete). The user may not delete random rows from random tables but they can execute a specific targeted delete procedure.

Tevo D
  • 3,351
  • 21
  • 28