Interesting post on the LinkedIn SQLDBA user group about row level security in MS SQL. Raju Pillai asked the following question:
I have a column with agency number , each agents under agency will have a userid to connect to the database. I want the agents to see only the rows that belong to their agency. I hate to create views for each agency against the table . What else can I do ?
James Hawkins response:
CREATE VIEW MyResults
INNER JOIN MyUsers ON MyRow.Agency = MyUsers.Agency
WHERE MyUser.Logon = SYSTEM_USER
Similarly, you might also want to explore the CURRENT_USER function.