May 12, 2010 at 8:14 am
I have a user that was given SSMS and permission to write his own queries. Unfortunately he is writing monsterous queries that are cripling the database server.
Is there a way to restrict his login to allow only one connection to SQL server? If he attempts to connect a second session it will be denied.
Thanks in advance.
Greg
Greg Roberts
May 12, 2010 at 8:21 am
1st of all, I would deny all access to a developer like this.
Even with only one connection, he can cripple the server.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 12, 2010 at 11:31 am
I know of no way to only allow one connection via a login. Through the server properties, yes, but not on the login level.
However, you could give this person access to a copy of your prod server (on a sandbox maybe, or a log-shipped / mirrored read only copy) and allow him to play there. This will give him the data he needs while protecting your production box and preventing him from taking down the server.
May 12, 2010 at 11:49 am
You could do it with a LOGON trigger, but you would need to be very careful..
You could set the trigger to look at all connections and only allow one connection OTHER than the one from "Microsoft SQL Server Management Studio", if it detects all the allowed connections from this user execute a ROLLBACK and I think the user logon is denied..
This is from BOL:
USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 3
ROLLBACK;
END;
It needs some tweaking but it is close..
CEWII
May 12, 2010 at 11:51 am
I want to make an additional point as well. This user has already shown that they can't be trusted even with a single connection, so another poster had a good suggestion of taking this access away and giving them access to a copy of the data. That would solve most of your problems.. And I would recommend that over a LOGON trigger.
CEWII
May 12, 2010 at 1:46 pm
I second Brandie and Elliot regarding the sandbox db.
Once that person "designed" a query he/she wants to run in production, that person should send the query to one of the more experienced folks (either DBA or developer) for approval. If it's ok, create a view/function/sproc he/she can use in production.
If it's not ok, explain what has been done wrong and how it should look like instead to get the same result.
Over time (sooner, maybe later) that person becomes an additional resource that kows the table structure and knows how to query it.
Another option might be to limit the resource given to this user (role) using resource governor. But this would require SS2K8...
May 12, 2010 at 6:40 pm
lmu92 (5/12/2010)
I second Brandie and Elliot regarding the sandbox db.Once that person "designed" a query he/she wants to run in production, that person should send the query to one of the more experienced folks (either DBA or developer) for approval. If it's ok, create a view/function/sproc he/she can use in production.
If it's not ok, explain what has been done wrong and how it should look like instead to get the same result.
Over time (sooner, maybe later) that person becomes an additional resource that kows the table structure and knows how to query it.
Another option might be to limit the resource given to this user (role) using resource governor. But this would require SS2K8...
I would disagree with this - I would not give them access to any system to run their queries until they have proven that they can write queries that won't take a system down.
Even on a copy/sandbox - unless that is dedicated to that one individual will cause problems for other users of that system.
Block all access until this person has been trained - and if that doesn't work, then all queries that need to be run for that person would go through the DBA to be run after a full review of the requirements.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 12, 2010 at 9:16 pm
Jeffrey Williams-493691 (5/12/2010)
I would disagree with this - I would not give them access to any system to run their queries until they have proven that they can write queries that won't take a system down.Even on a copy/sandbox - unless that is dedicated to that one individual will cause problems for other users of that system.
Block all access until this person has been trained - and if that doesn't work, then all queries that need to be run for that person would go through the DBA to be run after a full review of the requirements.
I'm sorry Jeffrey, but I have to disagree with you, at least partially. While I would love to be able to block all access, I have to work in the real world where I answer to people above me, and while I can push back strongly for production systems and almost certainly get my way, non-prod systems are a whole different beast. If this person has no need to be accessing the data then they should be disallowed, but if they have legitimate reasons I'm not going to have a lot of choices. Also training is nice but I've met a significant number of people who write terrible querys (IMO) that have been to training. Good query writing comes with time and experience, classes can help but are not a panacea. I look at some of my early code and cringe, I would NEVER write it that way now, but that was over 15 years ago, I've learned a few things since then.. Now the idea of running them through a DBA is not a bad idea, but again, not sure about practicality..
CEWII
May 14, 2010 at 3:32 am
You could try setting the query timeout on the server to a lower value.
At least then they are limited on how long their query will run and therefore impact your production system.
But this is obviously a server wide value so may be not possible.
Had a few people like this over the years myself and the only way I dealt with it was to hold their hands when they write queries and try and impose some best practise.
Also, if they are impacting other parts of the business get the heads of the impacted departments to have a word - you are no longer the bad guy.
Best of luck.
May 14, 2010 at 6:07 am
Can you set up a test environment? At least the faulty developer won't cripple the production server 😉
May 14, 2010 at 6:48 am
sorry didn't read the whole thread before I replied
yeah too bad your not on 2008, you could completely limit them with resource gov.
you could write a procedure that would load sp_who2 into a table varriable or a temp table, sort it by this persons log on, do a count of the open process for this user, and kill ever process whose ID was different than the first intial process.
you could set it up to run in SQL agent every couple minutes. but that would still leave a bit of a gap
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 17, 2010 at 11:04 am
Thank you all for the responses.
Unfortunately, a test envioronement will not provide any impact as this is a user in the finance department building his own reports, and he thinks execution times over an hour are normal and acceptable. The perception has been corrected. Somewhat.
The real answer is a proper datamart with preagregations, and an SSAS cube. But the consumers cannot wait for that to be developed. :ermm:
At least at this point he knows that if I come in in the morning and a query is running that he started before coing home the previous day, I'm going to kill it. With EXTREME prejudice. :hehe:
And yes, SQL 2008 is coming to our environment. So i should be able to take advantage of the new features when it is deployed.
Greg Roberts
May 17, 2010 at 12:23 pm
Just let "Bob" have unlimited access, then when the rest of the users complain about the slow system, make a point of telling them it's being slowed down by one of "Bob's" queries again. Encourage "Bob" to run as many queries as he wants as often as he wants, especially during peak work hours.
Soon, a mob of angry users will drag "Bob" out of the building and make an example of him.
May 18, 2010 at 5:19 pm
Presumably payment to this developer is authorized by someone in the organization.
Has this senior employee been advised and requested to provide the developer with "guidance"?
Also, try passing the complaints to the office of the Finance Director.
Directors tend to view complaints about their departments differently to others.
Something might then happen with the developer - or not.
May 19, 2010 at 4:27 am
Greg Roberts-134214 (5/17/2010)
Unfortunately, a test envioronement will not provide any impact as this is a user in the finance department building his own reports, and he thinks execution times over an hour are normal and acceptable.
My suggestion wasn't about correcting this user's behavior. It was about preventing this user from affecting others. The Test environment is the perfect way to fob him off on a db that he can't do any damage to.
I've encountered the user that training can't fix and that insists on playing power-user in the environment. The only way to protect yourself and your servers is to give him a play ground where he thinks he's in charge and let him run the all night reports to his heart's content.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply