Picking authentication when linking Access to SQL Server

[Don]

Hello, I have a Access mde that connects to SQL Server 2000 using
SQL Authentication mode. The application (mde) file has additional security
such as only granting certain users access to selected customer records.
I'm thinking about switching to Windows Authentication. This seems like the
better way
because you have to be logged onto the network before gaining access to SQL
Server. Also, when looking at the processes
executing against the SQL Server you'll see the network user id instead of
the SQL user.

Only issue I see with this is if you have a creative user that somehow has
installed query analyzer or using Access to directly link
to the SQL Server database. Now they will have access to all the customer
records. With SQL authentication the user would still need the SQL password
before getting direct access to the database. Is there someway to grant a
network user access (Using Windows Authentication) to a SQL Server database
but only if
it's accessed though a certain application (mde file)?

Thanks

Don

 

[Brendan Reynolds]

Don't grant the users read permission to tables. Give them read permission
only to views or stored procedures that return only the records the user is
authorized to see. For example here's a view that limits the records
returned by looking up the user name of the logged-in user in a table named
'User'. ('User' and 'Level' were not very good choices for table names on my
part, as they are both reserved words, but it works provided the names are
enclosed in square brackets.)

CREATE VIEW dbo.ClassView
AS
SELECT dbo.Class.*, dbo.[Level].LevelNumber, dbo.[Level].LevelName
FROM dbo.Class INNER JOIN
dbo.Teacher ON dbo.Class.TeacherID =
dbo.Teacher.TeacherID INNER JOIN
dbo.[Level] ON dbo.Class.LevelID = dbo.[Level].LevelID
WHERE (dbo.Teacher.SchoolID IN (SELECT SchoolID FROM [User] WHERE
UserName = SYSTEM_USER))

Look up 'SYSTEM_USER' in SQL Server Books On Line for more information.