What are the Pros and cons of row-level security Microsoft SQL Server

Here are the Pros and Cons for SQL server row-level security:

Pros Row-level security (RLS) reduces your application development complexity. Without RLS, you'd generally create filters with your queries or through ORM, or pass on filtering criteria to your procs. Once you have RLS in place, user1 sees what you desire user1 to see without changing your application much. With RLS, security is at the DB level. If another application was reading data in a different language from a different platform, RLS still allows user1 to see what you want user1 to see. If someone is trying to run/test SQL queries to SSMS, RLS shows data according to your setup. Your DB backup and restore will keep RLS intact.

Cons The disadvantage is more DB-centric work and a small amount of performance penalty. If you have web developers who aren't strong on the DB side, troubleshooting RLS might take them a bit of time. On the contrary, if the application layer was filtering information through an ORM, you could debug and write auditing on the application or middle-ware side.

To use RLS or not?  If your DB is well protected and access to data is through the application layer only, you can opt to build filtering within your application layer. It's not a lot of work to do security checks at the application layer AND use RLS. There isn't a one size fits all answer. For most simpler applications, I tend to use the application layer for filtering since it has served me well with auditing/logging/debugging.