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?

Question

We have a growing population of Access users, much to my chagrin! My question is whether there is a way of lowering these user’s priorities on the server/database. We are hoping that if the performance using Access is bad, that this will dissuade users from using Access.


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.




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |