Restrict user to Stored Procedures

Question

I need to restrict user access to SELECT, INSERT, UPDATE and DELETE, so that user should manage data only using stored procedures I provide.

So, for instance

SELECT * FROM Table1

should return

The SELECT permission was denied on the object 'Table1'

however, if there is stored procedure SelectTable1 defined as

CREATE PROCEDURE SelectTable1
AS
BEGIN
    SELECT * FROM Table1
END

(the real one contains filtering and parameters, so it is not meaningless, like the one above)

user should execute it successfully and get the resultset.

But obviously, I have no success implementing this set of permissions. Can anybody point me to some specific tutorial? MSDN was not very helpful.

Database is SQL Server 2012 and all objects (tables and stored procedures) are in custom schema.

Igor Borisenko · Answer 1 · 2013-09-16T07:24:41.063

3

You can do it using GRANT EXEC either on specific procedures or on schemas or on a database.

The following example grants EXECUTE permission on stored procedure HumanResources.uspUpdateEmployeeHireInfo to an application role called Recruiting11.

USE AdventureWorks2012; 
GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo
    TO Recruiting11;
GO

edited Sep 16 '13 at 07:24

answered Sep 16 '13 at 07:13

Igor Borisenko

3,806
3
34
49

This works, except I'm getting The SELECT permission was denied on the object 'Table1' when trying to execute procedure, just like with raw query. – adontz Sep 16 '13 at 07:18
1

@adontz Please read about ownership chains http://technet.microsoft.com/en-us/library/aa905173%28v=sql.80%29.aspx – Igor Borisenko Sep 16 '13 at 07:21

score 2 · Answer 2 · edited May 23 '17 at 12:02

Thanks to Igor I've got to the right MSDN page, and followed rights links.

However, using ownership chains suggested was too complicated for me, so I used

WITH EXECUTE AS OWNER

on my stored procedures and that works very good. When I log on using restricted user I see only procedures, no tables at all and I can execute procedures, but not even select from tables.

Also, I want to mention this concept is very similar to setuid and thus was familiar to me.

I mark Igors reply as answer, because ownership chains seem to be more generic way, just wanted to share info I found.

Restrict user to Stored Procedures

2 Answers2