Best practices using SQL 2016 Row Level Security with Stored Procedures

Doug Mann 21 Reputation points

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,734 questions
{count} votes

Accepted answer
  1. YufeiShao-msft 6,986 Reputation points

    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:

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 77,496 Reputation points MVP

    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
    0 comments No comments