How to grant permissions to single SELECT/UPDATE statment (sql server) ?

Question

I have a problem when trying to execute this update statement (below) using C# SqlCommand when I execute it locally it works fine, but when deployed to another machine I got a permission error

The SELECT permission was denied on the object Order ... The UPDATE permission was denied on the object Order ...

Update Statement

UPDATE Order SET Request = @request WHERE Id = @ID;

Is there any way to add GRANT SELECT and GRANT UPDATE to a single statement like the update above without using a stored procedure?

or the SP is the way to go on this?

Thanks in advance

Answer 1

You can't grant permissions on statements. You can grant SELECT, you can grant UPDATE, but on the table, not on the particular statements.

The best solution is to use stored procedures indeed. Execute the allowed SELECT and the allowed UPDATE from a stored procedure and use code signing to grant the necessary permissions, see Module Signing (Database Engine). Using code signing is more granular and better constrained than the alternative: relying on procedure and table common ownership for execution permissions.

Answer 2

Stored procedure is the way to go, then you can assign execute rights to that stored procedure without granting rights on the base table. SQL Server cannot assign rights to individual ad hoc SQL statements.