Saturday, November 16, 2013

How to force users to execute Stored Procedures only

Let's say there is a situation we want to prevent any SQL users from directly accessing any underlying tables and views. And we want SQL users to access underlying tables via stored procedures only. (I understand this is a special case and typically people don't want to do that.)

How can we implement this scenario?
If we only block one user, we first deny all DML operations against the user and then grant EXECUTE permission to the user as follows:

USE TestDB
DENY SELECT, INSERT, DELETE, UPDATE to User1
GRANT EXECUTE TO User1

If we want to apply this to any database users, we can use PUBLIC role as follows:

DENY SELECT, INSERT, DELETE, UPDATE to PUBLIC
GRANT EXECUTE TO PUBLIC


No comments:

Post a Comment