CODEWITHSUNDEEP
sqlsql-serversql-server-2012database-schemadatabase-permissions
adontz
adontz

Reputation: 1438

Restrict user to Stored Procedures

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.

Upvotes: 1

Views: 4303

Answers (2)

adontz
adontz

Reputation: 1438

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.

Upvotes: 2

Igor Borisenko
Igor Borisenko

Reputation: 3866

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

Upvotes: 3

Related Questions