SQL Server row filtering based on user

Does SQL Server support row filtering based on user? This is for SQL 2000  or SQL 2005. For example a table contains a column with distinct values 'X','Y' and 'Z'. User 1 can only see rows where the column = 'X', user 2 can only see rows where the column = 'Y' and user 3 can only see rows where the column = 'Z'.

The same SQL query must be run in all cases - I don't want to create three different views for example.

So all three users would run "select * from tablename" but they would all see different results.
purplesoupAsked:
Who is Participating?
 
ksaulConnect With a Mentor Commented:
You can do it with one view and use system_user to determine the current user:

CREATE View ViewName AS
SELECT ...
FROM ...
WHERE XYZColumn =
  CASE system_user
      WHEN 'User1' THEN 'X'
      WHEN 'User2' THEN 'Y
      WHEN 'User3' THEN 'Z'
END
0
 
Daniel WilsonConnect With a Mentor Commented:
If I am not much mistaken, you would create 3 views with different owners (in SQL 2000).

Create view User1.MyView
AS
Select field1, ... fieldn From TableName Where Fieldx = 'X'

go


Create view User2.MyView
AS
Select field1, ... fieldn From TableName Where Fieldx = 'Y'

go

etc.

Then when each user connects to the DB and issues "Select * from MyView" he would get his own view.

SQL2005 changes the owner / schema model.  It's supposed to be better, but I think you'd accomplish it a little differently there.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You should consider putting the details of the restrictions in a table, so you don't have to constantly recode and recompile views.  For example (only -- more design work almost surely needs done :-) ):

CREATE TABLE UserRestriction (
    code SMALLINT,  --diff. code assigned to each table column that must be compared against to restrict access
    username SYSNAME,
    value SQL_VARIANT,
    CONSTRAINT UserRestriction_CI
        UNIQUE CLUSTERED (code, username)
    )
INSERT INTO UserRestriction VALUES(1, 'User1', 'X')  --code 1 = table1.dataColumn5
INSERT INTO UserRestriction VALUES(1, 'User2', 'Y')
INSERT INTO UserRestriction VALUES(1, 'User3', 'Z')


CREATE VIEW ...
AS
SELECT ...
FROM table1
INNER JOIN UserRestriction ur ON ur.code = 1 AND table1.username = SUSER_SNAME() AND table1.dataColumn5 = ur.value
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.