Best practices using SQL 2016 Row Level Security with Stored Procedures

Question

With SQL Server 2016 the feature of "Row Level Security" for tables and views exists where I can filter with a table function and policy predicate. This works fine for SQL queries on tables, but what about stored procedures which use said tables. Instead of filtering out rows, it gives an exception. I would prefer the more graceful row filtering.

How do I work with RLS on sprocs and can someone please provide sproc examples?

Answer 1

Hi @Doug Mann ,

it seems that the exception you encounter may be about the permission.

You can grant permission to specific object:
Please login with an admin user that have rights to change execute permissions:

GRANT EXECUTE ON [dbo].[XXXXX] TO [UserYY]  

Grant permission to db_owner role:
Open SSMS>>Security>>Logins>>go to properties of user
Click to User Mapping>>check the database where you are going to give the db_owner role
Below this on Database role membership select db_owner role

Create a new database role:

CREATE ROLE db_executor  
--grant the role exector permission  
GRANT EXECUTE TO db_executor  

Giving everybody execution permission, but this is an extreme solution

GRANT Execute on [dbo].your_object to [public]  

Grant sysadmin server role to the user:
Open SSMS>>Security>>Logins>>go to properties of user
On Server Roles section check sysadmin

and the best practices of Row Level Security you can refer to:https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver15#Best

Answer 2

As Tom says, this has nothing to do with row-level security,

The normal routine is define a role, for instance:

CREATE ROLE db_executor

Then you can grant this role rights to run stored procedures. You can grant per procedure, but you can also do any of:

GRANT EXECUTE ON SCHEMA::dbo TO db_executor
GRANT EXECUTE TO db_executor

The first grants permission to all procedures in the dbo schema, including those added in the future. The latter grants permission on all stored procedures in the database. My preference is for the former.

Finally, you would add members to this role as you see fit:

ALTER ROLE db_executor ADD MEMBER me