We have a growing population of Access users. My question is whether there is a way of lowering these user's priorities on our SQL Server?

Answer

At our company, we forbid users from accessing any SQL Server databases using Microsoft Access. We do this because of extra overhead put on SQL Server from using Access as a client, and because most of these user's don't really understand what they are doing, and often create queries that unnecessarily use up SQL Server resources.

Unfortunately, there is no way to identify Access users and assign them a lower priority. All connections are created equal, and all connections have the same access to SQL Server resources.

The closest you can come to achieving this is to use the "query governor cost limit option". This option is used to specify the maximum amount of time (in seconds) that a query can run. If the query analyzer determines that if any query will exceed the maximum time, then that query will be aborted by SQL Server. Unfortuantely, this option affects all SQL Server users, not specific users, such as Access users.

The easiest way to keep them out is to deny then access in the first place.