Using Views to Control User Access to SQL Server Data

I have a Microsoft Access 2000 application with SQL Server 2000 as the back end. To prevent Access users from viewing all the data in a SQL Server 2000 table, I want to use a view that displays only data rows that the user has been authorized to see. Do SQL Server views support such a scenario?

Yes, you can create a view that limits user access to SQL Server data—if each user logs in to Access with a unique userid. The following sample statement creates such a view:

CREATE VIEW v_data AS   SELECT        FROM dbo.mytable AS a      INNER JOIN dbo.authtable AS b      ON (a.Pkey = b.DataKey AND b.userid = suser_sname())

This view will restrict access based on userid and will require you to maintain a table (authtable) with the user name matched to specific primary keys in the data table (mytable). If your situation is less complex and doesn't require you to manage row access for multiple users, you could insert the userid column into the data table, as the following code shows:

CREATE VIEW v_data AS   SELECT        FROM dbo.mytable AS a      WHERE a.userid = suser_sname()