Can you grant permissions on rows? 1

[j9]

I have several stored procedures that currently accept the parameters 'year' and 'department'. Instead of using a parameter for 'department', I would like to determine the department based on the user's login. For instance, if John logs in, the stored procedure should only retrieve data from table1 where table2.department = English, but when Alice logs in, it should only retrieve data from table1 where table2.department=French. Is there any way to do this without creating different stored procedures for different departments?

 

[rac2]

The T-SQL function USER_NAME() returns the name of the current user. If you have a table with SQL Server user names and departments then it should be straightforward to write a WHERE clause that limits rows to the user's department. For example,

WHERE table1.department =
    (SELECT department FROM table2 WHERE emp_sql_user_name = USER_NAME())