Blocking Access from linking tables...

[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
MW****@FirstByte.net

[Eric Schittlipz]

"Matthew Wells" <MW****@FirstByte.net> wrote in message
news:3_******************@newsread3.news.atl.earth link.net...

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
MW****@FirstByte.net

In fact, if you've allocated db_datareader and db_datawriter permissions for
a Windows account, users could easily write a script to mess up your data.
Or they could create a new Access project (adp) file with only a few clicks,
without any prompts for passwords and with no knowledge of DSNs or anything.
They need only pretty basic Access knowledge to do this.
But presumably they have these permissions because they are trusted
individuals, and their Windows logins also give them permission to do other
potentially harmful things, such as edit or delete shared Excel spreadsheets
or Word documents. Yet, I imagine, they manage to contain themselves.
If you really want to make things more secure, then you need to be less
generous with your permissions and create a database role which only has
permissions for selected stored procedures - not blanket permissions for all
tables. This would be a first big step to improving security, but it may
mean re-writing parts of your application.

[Alan Lane]

Hi Matthew:

Have you looked into creating a SQL Server "Application Role" for your
Access app? The idea is that your users would only be able to get to the
SQL Server tables through your application. They would not have any
independent ability to get to those tables outside of your application
(unless you gave it to them). Would something like that be an answer to
your problem?

Alan
"Matthew Wells" <MW****@FirstByte.net> wrote in message
news:3_******************@newsread3.news.atl.earth link.net...

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
MW****@FirstByte.net