I have a bunch of users making ad-hoc queries into a database running SQL Server. Occasionally someone will run a query that locks up the system for a long period of time as they retrieve 10MM rows.

Is it possible to set some options when a specific login connects? e.g.:

  • transaction isolation level
  • max rowcount
  • query timeout

If this isn't possible in SQL Server 2000, is it possible in another version? As far as I can tell, the resource governor does not give you control like this (it just lets you manage memory and CPU).

I realize the user's could do much of this themselves but it'd be awesome if I could control it from the server per user.

Obviously I'd like to move the users away from direct table/view access but that's not an option at the moment.

+2  A: 

You can certainly limit the query results. I'm doing it for StackQL using a stored procedure that looks something like this:

CREATE PROCEDURE [dbo].[WebQuery] 
    @QueryText nvarchar(1000)

    INSERT INTO QueryLogs(QueryText) 


    Begin Try
     exec (@QueryText) 
    End Try
    Begin Catch
     SELECT ERROR_NUMBER() AS ErrorNumber, 
      ERROR_MESSAGE() AS ErrorMessage,
      ERROR_PROCEDURE() AS ErrorProcedure,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_LINE() AS ErrorLine,
      ERROR_STATE() AS ErrorState
    End Catch


The important part here is the series of three SET statements after the log. This limits based on both the number of rows in the results and the expected costs of the query. The rowcount and query governor values can be use variables, and so it shouldn't be hard to modify that to change the restriction based on the current user as well.

However, you should also note that it's pretty easy for users who are "in the know" to bust out of that if they want. In my case I consider the ability to get past the limits from time to time a feature. But it's also why I do the logging: the code to get past the limits sticks out in the logs, and so I can easily catch and ban anyone doing it too often without my permission.

Finally, any user that calls this should be only in the denydatawriters role, the datareaders role, and then given explicit permissions to execute just this stored procedure. Then they can't really do anything but select on existing tables.

Now, I'll anticipate your next question is whether you can make this automatic from somewhere like report builder or management studio. Unfortunatley, I don't think that's possible. You'll need to give them some kind of interface that makes it easy to call your stored procedure. But I could be wrong here.

Joel Coehoorn
@Joel: So instead of letting users run queries *directly* against the database, I could let them use this thin wrapper? I like it, thanks! And yes, you nailed my next question.
Michael Haren
+1  A: 

In SQL Server 2005 and 2008 you can add a LOGIN Trigger to the database that could change the connections settings, based on who they are connected as. However, you need to be very careful with these as a mistake or error in the trigger could result in everyone being locked out.

There's nothing like that in SQL Server 2000.

I just wanted to add, that if Joel's approach works for you, then I would strongly encourage that method over mine, because a Login Trigger is one mighty big and dangerous grenade to be throwing at this problem.

If you still really want to do them, here is a nice article that demonstrates how to use them. Even more important, however is here which has the crucial instructions for what to do if your Logon Trigger locks everyone out.

+1 Thanks for this, it looks promising. It would benefit from an example, though.
Michael Haren
Very useful, thanks!
Michael Haren