tsql - SQL Server - Return rows based on user role -

we developing access application sql server backend. have table has records belong division a, b or c. users belong role a, b or c. want each user see corresponding division records, columns.

i've thought of 2 ways, 1 making different queries each role , then, based on user's role, change source object of form. don't know if possible retrieve sql server vba (all vba documentation i've found far quite lacking).

the other solution thought implement on server, don't know how t-sql query or view fetch information needed based on user's role

any ideas?

ps: can't use functions or stored procedures. reason sql server have been provided has them disabled , ops won't enable them (don't know logic behind that).

okay, it's been while since posted i'll post solution came in end. vba not quite necessary in case. can done views.

to retrieve users roles, (inner) join table database_role_members twice database_principals one. join id (from database_principals) on both fields. this, list of roles , corresponding users. roles of user querying database add clause checks user name corresponds function user_name.

then, don't give permission roles access table want restrict access to. instead, make view fetches info table , add clause looks value column against query retrieves user roles.

with can make link in access view , allow see records correspond user roles.

while approach easy, doesn't allow more complicated row level security. more powerful approach might useful check following link.

https://msdn.microsoft.com/en-us/library/dn765131.aspx