October 16, 2014 at 2:34 pm
Alexander Suprun (10/16/2014)
Create a job which runs every 5 seconds and KILL the processes of specific user(s) where program_name is 'Microsoft SQL Server Management Studio - Query'
And hope they don't learn how easy it is to change the program name when connecting a query window.
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
October 16, 2014 at 2:35 pm
Probably the easiest way to do that is to create views which limit the data by date or whatever, then give them access to only the views. But that could be a hard sell at this point.
You could also rename the existing table, and make the original table name a restricted view. But that would (likely) require changing all your existing code to refer to the new table name, which might be extremely difficult if not impossible.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 16, 2014 at 2:37 pm
Darryn_the_Tired (10/16/2014)
Devs have told me they cant change this and its my problem.
Time to get management involved?
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
October 16, 2014 at 2:49 pm
GilaMonster (10/16/2014)
Darryn_the_Tired (10/16/2014)
Devs have told me they cant change this and its my problem.Time to get management involved?
HA!
“Program (pro’ gram) n. a logical sequence of operations to be performed by a computer that usually results in error messages, v.t. to engage in an activity similar to banging one’s head against a wall.”
October 16, 2014 at 2:55 pm
Devs have told me they cant change this and its my problem.
Start telling anyone that complains about the problem that it's working as intended and they need to be talking to the devs and not you if they don't like that.
October 16, 2014 at 2:58 pm
Is this person named Paul, by any chance? 😀
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 16, 2014 at 4:02 pm
I'd raise a stink internally. You can't work miracles. There are physical limitations to the universe and a query without a WHERE clause is going to smack right into them pretty hard.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 16, 2014 at 4:28 pm
Grant Fritchey (10/16/2014)
I'd raise a stink internally. You can't work miracles. There are physical limitations to the universe and a query without a WHERE clause is going to smack right into them pretty hard.
Yup. My suggestion of management wasn't intended to be funny.
You can' fix this without the dev team's cooperation. If they're not willing to cooperate....
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
October 16, 2014 at 10:01 pm
Not looking good...
Thanks all for taking the time to think about this...much appreciated
“Program (pro’ gram) n. a logical sequence of operations to be performed by a computer that usually results in error messages, v.t. to engage in an activity similar to banging one’s head against a wall.”
October 17, 2014 at 2:36 am
Think about it for a second. If you could somehow achieve what you wanted, all that will happen is that the same client will do the same thing, but get no data back--at which point they'll complain to the developers, who, from the sounds of it, will bat it right back at you again. You're no further along.
To be honest, if your developers really aren't capable of coding in a simple message that says "Sorry, you must select some filters first" if a client hits Get Data with no filters selected, you need to get some new developers.
October 17, 2014 at 2:40 am
paul.knibbs (10/17/2014)
To be honest, if your developers really aren't capable of coding in a simple message that says "Sorry, you must select some filters first" if a client hits Get Data with no filters selected, you need to get some new developers.
+1 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 17, 2014 at 2:58 am
Quite possible a business rule exists, allowing users query DB with no filters set. And there're users and users. So it's a management problem really. Teach users or buy more processors or change rules.
October 17, 2014 at 3:07 am
The other option is to simply throw hardware at the problem, but that's very expensive and of limited utility. You're going to have to convince people about the realities of the situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 21, 2014 at 7:54 am
If they're allowed to specify the "from <table> join <some other table>" part, then it's not just the lack of a WHERE clause that you need to worry about. I assume they have read-only access so they can't corrupt data, but unrestricted read access is still wrong in many (most) situations.
October 22, 2014 at 6:32 am
Well you could do it with security. The application is either using a SQL login, a single AD login or service account, or it's passing through end user credentials. Deny those users access to the tables then they won't show up in the application. Then if they need access, create some similarly named views that all have a nice default where clause, like last 2 months as someone suggested.
To be honest, your devs sound a bit closed minded. What they should really be doing is some sort of paging. Either controlled by the code, or by injecting an ORDER BY with OFFSET and FETCH http://msdn.microsoft.com/en-us/library/ms188385.aspx
see the examples at the end of that msdn entry on the ORDER BY clause.
To be fair, if the users have been provided with a tool that is open slather for creating select * queries, then they should be provided with training on those elevated rights and their responsibilities with regard to the data. It's a privilege not a right to have that level of access
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply