Blocking MS Access linked tables...

  • Thread starter Thread starter Matthew Wells
  • Start date Start date
M

Matthew Wells

Good morning...

I have an Access front end that uses SQL Server linked tables. SQL Server
uses Windows authentication. I have one Windows group that all Access users
are a member of. I added that group to SQL Server logins and gave it
public, datareader, and datawriter rights to the one database that's used.
My front end is locked down, but I want to stop users from creating a new
..mdb and linking SQL Server tables through DSNs or ADO connections or even
just importing the links from the actual front end.. I've tried setting the
"denydatareader" security policy - that keeps the SQL tables from being seen
in the import/link list- but also blocks read rights from the actual front
end database. I could set an Access database password on the front end to
block importing the links, but that only solves one of the three problems
and I want to stay away from Access security altogether.

Is there a way to stop users from creating their own DSNs or connection
objects or linking tables while still using Windows authentication?

Thanks.

Matthew Wells
(e-mail address removed)
 
One way to control users' access (write/read...) is to use Stored
Procedures, i.e. all data access from your front end app is through SPs in
SQL Server, users do not have any right on creating/writing/reading to
tables directly, they are only give permission to the SPs you created for
the front app. Thus, even they have Access installed on their machine and
some of them are smart enough to be able to start a new *.mdb/*.adp and
connect Access to SQL Server, they cannot do anything but executing the SPs
you have designed. I do not know if it poses some difficulties to use SPs
only for a linked front end (*.mdb), but I do know that if you use *.adp,
there is no problem. Actually, it is a preferable to use *.adp as SQL
Server's front end: it is specially designed for this type of application by
MS (since Access2K).
 
Matthew Wells said:
Good morning...

I have an Access front end that uses SQL Server linked tables. SQL Server
uses Windows authentication. I have one Windows group that all Access users
are a member of. I added that group to SQL Server logins and gave it
public, datareader, and datawriter rights to the one database that's used.
My front end is locked down, but I want to stop users from creating a new
.mdb and linking SQL Server tables through DSNs or ADO connections or even
just importing the links from the actual front end.. I've tried setting the
"denydatareader" security policy - that keeps the SQL tables from being seen
in the import/link list- but also blocks read rights from the actual front
end database. I could set an Access database password on the front end to
block importing the links, but that only solves one of the three problems
and I want to stay away from Access security altogether.

Is there a way to stop users from creating their own DSNs or connection
objects or linking tables while still using Windows authentication?

Thanks.

Matthew Wells
(e-mail address removed)
Click to expand...

You didn't mention which OS the users have. But if it is a secure OS, like
NT/2K Pro/XP Pro, then you should use local security policy to prevent them
from running the ODBC Administrator. Also, you could give them only a
runtime version of Access.
 
Back
Top