Sql – force certain settings by user in SQL Server

interfacesql-serversql-server-2000

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.

Best Solution

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)
AS
BEGIN

    INSERT INTO QueryLogs(QueryText) 
        VALUES(@QueryText)

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SET QUERY_GOVERNOR_COST_LIMIT 15000 
    SET ROWCOUNT 500 

    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

END

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.

Related Question