[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server row filtering based on user

Posted on 2007-07-30
3
Medium Priority
?
164 Views
Last Modified: 2010-03-19
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.
0
Comment
Question by:purplesoup
3 Comments
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 400 total points
ID: 19592714
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
 
LVL 10

Accepted Solution

by:
ksaul earned 1200 total points
ID: 19593198
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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 19596008
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question