November 20, 2009 at 4:02 pm
We are planning to open up access to our reporting server (i.e. non-production SQL database) to a broader range of users who could write their own SQL code and pull their own reports. This way they won’t have to wait on our small and overstretched department for their more trivial requests. These people know just enough SQL to be dangerous. They would, of course have only read access ([font="Courier New"]SELECT[/font]). However, even with read-only access, it appears that users can still use Hints to obtain exclusive locks, like [font="Courier New"]TABLOCKX[/font] and [font="Courier New"]XLOCK[/font]. We are concerned that someone might try using it and end up locking people for the duration of their possibly long running query – almost a DoS (Denial of Service) attack, if you will. Even though this is a non-production server, we don’t want this to happen.
Does anyone have any suggestions for preventing users from obtaining exclusive locks? If not, do you have any suggests for automatically killing such queries after the fact, like a script that SQL Agent runs every 5 minutes, or something like that?
Thank you so much for your help and time.
November 23, 2009 at 8:00 am
How are you populating the Reporting Database? If through a daily load you could make it a read only database and then I believe the hints will be ignored.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 23, 2009 at 8:45 am
How are users connecting?
My short answer is that if you can force them to run dynamic SQL through a proc of some sort, you could use REPLACE to strip out those hints.
November 23, 2009 at 12:06 pm
The database is populated by downloading the changes (based upon last change [font="Courier New"]DATETIME[/font] columns). We're talking hundreds of gigabytes, so re-downloading everything everyday isn't an option. Also, we don't own the production database. We pay a third-party for the use of their software and access to their production database, which we download to our own reporting server.
Users would be connecting through MS SQL Management Studio Express, or if they have a license for some other software they would prefer to use, then through an ODBC connection.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply