Limit user to application

i looking new features in 2016 , came across row level security.

i have wanted limit access on users within application or reporting services. not want users able access other predefined accesses built queries. in other word when work within limits of our application, granted full access limited application. no access should permitted when working ad-hoc via other tool. 

applications access specific database, run cross database queries well. use synonyms access outside of main db db may on server.

can row level security me place limits on external db access , how implement this?

---

???

we have 3-tier application. users login , verified ad. business web server connects db via sql user unknown users. calling server web server. problem the db server sees web user , cannot control permission having trust application manage security user. love add groupings users defines areas , allow data within area visible users.

you use row-level security based on application name, since apparently out of reach users manipulate this.

however, there new feature in sql 2016 may serve better, possibly in combination row-level security.

the command set context_info has been in product long time. command permits set varbinary(128) value can retrieve function context_info(). applications yours when middle-tier server authenticates users , logs in standard users, common use set context_info , context_info() pass name of actual users.

in sql 2016, have enhanced no longer restricted 128 bytes. use stored procedure sp_set_session_context set key value pair, , can later retrieve values function session_context(). is, when user connects issue 1 or more calls sp_set_session_context set values can read row-level security filters. careful not add network roundtrips, costly if move database cloud.

SQL Server  >  SQL Server Security