How to restrict Users from running SQL.
-
We use SQL Server 2005. We have created a SQL Server Login called RUNSQLQUERYUSER. We have the following questions:
1. How to limit RUNSQLQUERYUSER from executing SQL queries using SQL Server Management Studio between 9:00 AM and 5:00 PM? How to implement that? Therefore if RUNSQLQUERYUSER runs a query at 8:00 AM we let them
but after 9:00 AM we don't let this user execute queries.
2. If RUNSQLQUERYUSER runs a SQL query before 9:00 AM but the query takes more than 30 minutes we need to stop it.
3. If RUNSQLQUERYUSER runs a SQL query before 9:00 AM but the query is still running at 9:00 AM we need to stop it.
-
for #2 & 3, why not create a new/different user, and use that for the off hours processing?
have you determined what the impact would be if you killed the user @9am, or was running more than 30 minutes? I've seen transactions take 3 times as long to rollback as it did to run before it was killed.
the better solution is to fix those slow running queries in the first place...i'd bet 5 dollars right now it involves cursors.(which with some smart coding can be removed and see one or two orders of magnitude performance enhancement.
for #1, the right thing to do is to change the password so noone can use it in the first place...an application should use an application role, so the password is never used at all, or at least should read the encrypted value from a config file, isntead of everyone knowing they can use SSMS.
a crappy work around for #1, is to try to create a logon trigger that detects the application name, but that's not guaranteed, as it can be set by the person runnning SSMS if they know what they are doing.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
A scheduled job that runs at 9am, disables the login and kills any active sessions using it. Another job that runs at 5pm and enables it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
KP-249943 (8/31/2012)
We use SQL Server 2005. We have created a SQL Server Login called RUNSQLQUERYUSER. We have the following questions:1. How to limit RUNSQLQUERYUSER from executing SQL queries using SQL Server Management Studio between 9:00 AM and 5:00 PM? How to implement that? Therefore if RUNSQLQUERYUSER runs a query at 8:00 AM we let them
but after 9:00 AM we don't let this user execute queries.
2. If RUNSQLQUERYUSER runs a SQL query before 9:00 AM but the query takes more than 30 minutes we need to stop it.
3. If RUNSQLQUERYUSER runs a SQL query before 9:00 AM but the query is still running at 9:00 AM we need to stop it.
To emphasize what Gail and Lowell have already written...
Gail has the near perfect solution for #1. It's simple and it's effective. Killing SPIDs may cause a problem, though. You'll see more on that in a second.
#2 and #3 are a different story. As Lowell stated, rollbacks from killing a query (SPID) can take a whole lot longer than you might expect. In fact, if the query relates to a linked server or has something like an OPENQUERY in it, SQL Server has a fault that may cause a rollback that runs forever until the next bounce of the service. These are easily identified as a rollback that has done 0% work but shows no work being accomplished in the log. The big problem with these rollbacks is that they will typically consume the resources of an entire CPU and they can't be fixed without bouncing the service.
Further, killing long running queries doesn't really address the root problem of that issue. The root problem is that it [font="Arial Black"]IS[/font] a long running query and it needs to be fixed. If the users need to run the same query every night, then helping them by writing an optimized stored procedure and scheduling it for them would be the way to go.
If the users typically write nonrepeatable ad hoc queries a lot and they take a long time to run, then consider creating a "reporting" server that's kept up to date either by SQL Server replication or, better yet, SAN replication.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I tend to agree with what Jeff said. Fix the code where possible.
You can look at a job that kills the queries after some time, but there will be a rollback.
The problem isn't that there is a query that runs long. The problem is it interferes with other work, not that it runs long.
Look at the code. Rewrite it where possible to make it more concurrent.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply