Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to restrict Users from running SQL. Expand / Collapse
Author
Message
Posted Friday, August 31, 2012 1:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 31, 2012 3:42 PM
Points: 19, Visits: 262
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.
Post #1353073
Posted Friday, August 31, 2012 2:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1353082
Posted Friday, August 31, 2012 4:01 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 43,025, Visits: 36,189
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1353110
Posted Saturday, September 1, 2012 12:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 37,099, Visits: 31,649
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 IS 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353161
Posted Sunday, September 2, 2012 10:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:38 AM
Points: 33,267, Visits: 15,436
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1353242
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse