Using views to control user access to data

Access | control | view | data

Q: My Microsoft Access 2000 application writes data from a back-end SQL Server 2000 database. To prevent users of access from seeing all of the data in a SQL Server 2000 table, I want to use a view that allows users to browse only authorized rows of data. Can you create a view that restricts user access to SQL Server data?

Answer: Yes. If each user logs on to access with a unique user ID, you can create a view that restricts user access to SQL Server data. The following example statement can create a view that:

CREATE VIEW V_data as
SELECT <column_list>
From Dbo.mytable as a
INNER JOIN Dbo.authtable as B
On (A.pkey = B.datakey
and B.userid = SUSER_SNAME ())

This view restricts access to users by UserID. It requires you to save a table (authtable) that matches the user name of a particular primary key in the datasheet (mytable). If your situation is relatively simple-you do not need to manage row access for multiple users, you can insert the UserID column into the datasheet, as shown in the following code:

CREATE VIEW V_data as
SELECT <column_list>
From Dbo.mytable as a
WHERE A.userid = SUSER_SNAME ()

-microsoft SQL Server Development Team