Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to restrict Users from running SQL.


How to restrict Users from running SQL.

Author
Message
KP-249943
KP-249943
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14967 Visits: 38985
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!

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47287 Visits: 44392
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 recoverability

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


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45163 Visits: 39924
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36176 Visits: 18751
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search