General question concerning Users and allowing SQL queries on a database

Question

General question concerning Users and allowing SQL queries on a database

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101103 More actions · Answer 1

Ed Wagner (8/28/2015)
Scott, I agree with you that the point is to run a business. You raise a good point about perspective. It may be that cutting off all access could be bad for business, but so could having everyone with a computer having the ability to run whatever queries they want against the production database. It might impact performance...just a bit anyway.
Going back to a previous post, the DBA and management needs to have a serious conversation, focused on the needs and wants of the employees.
As for me, I'd vote no.

"Everyone"? I thought in this case it was limited to strictly the one manager.

At any rate, yes, I differ from most here, because my default vote is yes, barring specific reasons not to.

If the data is large, maybe query governor is available; if so, that could be an option.

Another possible option is SSRS. it has such good parameter support that it can be a basic ad-hoc reporting tool.

Finally, if people really need to run things ad-hoc, you may need to set read_committed_snapshot in the db and adjust tempdb accordingly.

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".

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 2

Hmm... from the what was described it sounds like the end users already have access to reporting features through their application but aren't super happy with it. Without knowing the exact requirements and capabilities of their application it's hard to say what should be done, but in general just give everyone full access to everything strikes me as bad.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

ScottPletcher (8/28/2015)
The point is to run a business, not have a perfect environment for the db itself.
Yes, much care needs to be taken before allowing anyone to run ad-hoc queries. But blocking all access could be a very severe mistake as well. Don't lose sight of the real issue: what's best overall for the business.

I absolutely agree except that IS possible to have your cake and eat it too. And that's a great segue into the much more important topic. As DBAs, one of our jobs is to make the data accessible to those that have the need and are authorized to get at the data. That's where the world usually comes crashing down because a lot of DBAs just give the users privs and then gripe about their impact on the production server instead of doing something about it.

To wit, most users don't write truly ad hoc queries. Normally, they've written a query where they go in and change a date range or a department number or some such similar. What the DBA needs to do (and, yes, I do it) is to 1) identify the code if it becomes a performance issue, 2) fix the code and possibly turn it into an easy to use stored procedure, iTVF, or View, 3) get with the user and show them how to use the fix, 4) possibly teach the user why the changes to the code was necessary, and 5) ask the user to submit future code for review so you can help them avoid performance or accuracy problems. A lot of DBAs don't even try to do step 1 and, of those that do, never make it past step 2.

This also gives me the opportunity to again print David Poole's wonderful quote of "If you're the first person that people seek out for database help rather than the last, you might be an Exceptional DBA".

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

ZZartin (8/28/2015)
Jeff Moden (8/27/2015)
Heh... you guys are all missing it. You should encourage all manner of ad hoc queries from the masses so that you can better justify a massive hardware and training budget not to mention all the cool toys to make SQL Server "run faster" even though it won't really help much. 🙂
Can't we just give everyone the SA password too? Then we'll never even have to worry about permission issues!!!

I worked at that company... for nine whole months. What a nightmare.

"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

ScottPletcher SSC Guru Points: 101103 More actions · Answer 5

Jeff Moden (8/28/2015)
ScottPletcher (8/28/2015)
The point is to run a business, not have a perfect environment for the db itself.
Yes, much care needs to be taken before allowing anyone to run ad-hoc queries. But blocking all access could be a very severe mistake as well. Don't lose sight of the real issue: what's best overall for the business.
I absolutely agree except that IS possible to have your cake and eat it too. And that's a great segue into the much more important topic. As DBAs, one of our jobs is to make the data accessible to those that have the need and are authorized to get at the data. That's where the world usually comes crashing down because a lot of DBAs just give the users privs and then gripe about their impact on the production server instead of doing something about it.
To wit, most users don't write truly ad hoc queries. Normally, they've written a query where they go in and change a date range or a department number or some such similar. What the DBA needs to do (and, yes, I do it) is to 1) identify the code if it becomes a performance issue, 2) fix the code and possibly turn it into an easy to use stored procedure, iTVF, or View, 3) get with the user and show them how to use the fix, 4) possibly teach the user why the changes to the code was necessary, and 5) ask the user to submit future code for review so you can help them avoid performance or accuracy problems. A lot of DBAs don't even try to do step 1 and, of those that do, never make it past step 2.
This also gives me the opportunity to again print David Poole's wonderful quote of "If you're the first person that people seek out for database help rather than the last, you might be an Exceptional DBA".

As I noted above, those could easily be changed into SSRS reports.

The rest is fine if you can do it. That depends on the number of users and/or where they are. I suppose I could try to set up video links with Britain, Ireland, France and multiple places in Asia over time to do all that "meeting with users", but I extremely strongly suspect it wouldn't be approved anyway :-D.

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".

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 6

Ed Wagner (8/28/2015)
Scott, I agree with you that the point is to run a business. You raise a good point about perspective. It may be that cutting off all access could be bad for business, but so could having everyone with a computer having the ability to run whatever queries they want against the production database. It might impact performance...just a bit anyway.
Going back to a previous post, the DBA and management needs to have a serious conversation, focused on the needs and wants of the employees.
As for me, I'd vote no.

I struggle with this a great deal on my end because the data is primarily used for reporting only. While it's good to restrict, the end users who pay your salary may defy everything holy and still insist due to lack of understanding the reproductions of their actions.

I get around this now by simply providing reporting data marts for the business case. The only issue is the bottleneck of creating a the data mart, but at least it's separate, read only and totally secure for only the end users for that specific business case.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

xsevensinzx (8/29/2015)
Ed Wagner (8/28/2015)
Scott, I agree with you that the point is to run a business. You raise a good point about perspective. It may be that cutting off all access could be bad for business, but so could having everyone with a computer having the ability to run whatever queries they want against the production database. It might impact performance...just a bit anyway.
Going back to a previous post, the DBA and management needs to have a serious conversation, focused on the needs and wants of the employees.
As for me, I'd vote no.
I struggle with this a great deal on my end because the data is primarily used for reporting only. While it's good to restrict, the end users who pay your salary may defy everything holy and still insist due to lack of understanding the reproductions of their actions.
I get around this now by simply providing reporting data marts for the business case. The only issue is the bottleneck of creating a the data mart, but at least it's separate, read only and totally secure for only the end users for that specific business case.

Well, it's good that you're talking about a separate database for reporting. If they're isolated from impacting the performance of the production system, then they won't take anything down. I hope (for your sake) that they know how to write queries to produce accurate results.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 8

Ed Wagner (8/29/2015)
xsevensinzx (8/29/2015)
Ed Wagner (8/28/2015)
Scott, I agree with you that the point is to run a business. You raise a good point about perspective. It may be that cutting off all access could be bad for business, but so could having everyone with a computer having the ability to run whatever queries they want against the production database. It might impact performance...just a bit anyway.
Going back to a previous post, the DBA and management needs to have a serious conversation, focused on the needs and wants of the employees.
As for me, I'd vote no.
I struggle with this a great deal on my end because the data is primarily used for reporting only. While it's good to restrict, the end users who pay your salary may defy everything holy and still insist due to lack of understanding the reproductions of their actions.
I get around this now by simply providing reporting data marts for the business case. The only issue is the bottleneck of creating a the data mart, but at least it's separate, read only and totally secure for only the end users for that specific business case.
Well, it's good that you're talking about a separate database for reporting. If they're isolated from impacting the performance of the production system, then they won't take anything down. I hope (for your sake) that they know how to write queries to produce accurate results.

The assumption that users don't know how to access the data or query the data highly depends on the organization. My users actually find it insulting when DBA's assume they don't know anything about the data or the services used to manage/serve that data for them on a daily basis.

This is primarily because my users are heavy into analytics. They read and write in various scripting languages and code. While they are not experts in the database, specifically SQL Server, they have been educated enough by myself to know how to query the database good enough.

So, playing the card that you don't know any better and we must protect you from yourself for everyone's good does not fly in some organizations. Especially being what we do is not exactly rocket science.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

xsevensinzx (8/29/2015)
Ed Wagner (8/29/2015)
xsevensinzx (8/29/2015)
Ed Wagner (8/28/2015)
Scott, I agree with you that the point is to run a business. You raise a good point about perspective. It may be that cutting off all access could be bad for business, but so could having everyone with a computer having the ability to run whatever queries they want against the production database. It might impact performance...just a bit anyway.
Going back to a previous post, the DBA and management needs to have a serious conversation, focused on the needs and wants of the employees.
As for me, I'd vote no.
I struggle with this a great deal on my end because the data is primarily used for reporting only. While it's good to restrict, the end users who pay your salary may defy everything holy and still insist due to lack of understanding the reproductions of their actions.
I get around this now by simply providing reporting data marts for the business case. The only issue is the bottleneck of creating a the data mart, but at least it's separate, read only and totally secure for only the end users for that specific business case.
Well, it's good that you're talking about a separate database for reporting. If they're isolated from impacting the performance of the production system, then they won't take anything down. I hope (for your sake) that they know how to write queries to produce accurate results.
The assumption that users don't know how to access the data or query the data highly depends on the organization. My users actually find it insulting when DBA's assume they don't know anything about the data or the services used to manage/serve that data for them on a daily basis.
This is primarily because my users are heavy into analytics. They read and write in various scripting languages and code. While they are not experts in the database, specifically SQL Server, they have been educated enough by myself to know how to query the database good enough.
So, playing the card that you don't know any better and we must protect you from yourself for everyone's good does not fly in some organizations. Especially being what we do is not exactly rocket science.

It absolutely depends on the people who was access to the data. I don't care if they're management, users, developers, QA or DBAs. It always depends on the people.

In your situation, the fact that they understand data is a good thing. if they're educated on how to do things, then that's a great thing. Well done. They must understand, however, that if they write a query that produces inaccurate results, then that's their responsibility and they can't go blaming others for the fact that they don't know how to query data.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 10

xsevensinzx (8/29/2015)
Ed Wagner (8/28/2015)
Scott, I agree with you that the point is to run a business. You raise a good point about perspective. It may be that cutting off all access could be bad for business, but so could having everyone with a computer having the ability to run whatever queries they want against the production database. It might impact performance...just a bit anyway.
Going back to a previous post, the DBA and management needs to have a serious conversation, focused on the needs and wants of the employees.
As for me, I'd vote no.
I struggle with this a great deal on my end because the data is primarily used for reporting only. While it's good to restrict, the end users who pay your salary may defy everything holy and still insist due to lack of understanding the reproductions of their actions.
I get around this now by simply providing reporting data marts for the business case. The only issue is the bottleneck of creating a the data mart, but at least it's separate, read only and totally secure for only the end users for that specific business case.

One of the dangers of letting end users create their own reports is that they will, for example end user A goes in and sets up a report doesn't get the results expected and tweaks his query until he does get the results he wants but user B goes in and sets up a query for the same reason but doesn't tweak it. Now they both show up to a meeting with conflicting data and it's the DBA's problem to fix not their's.

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 11

ZZartin (8/29/2015)
xsevensinzx (8/29/2015)
Ed Wagner (8/28/2015)
Scott, I agree with you that the point is to run a business. You raise a good point about perspective. It may be that cutting off all access could be bad for business, but so could having everyone with a computer having the ability to run whatever queries they want against the production database. It might impact performance...just a bit anyway.
Going back to a previous post, the DBA and management needs to have a serious conversation, focused on the needs and wants of the employees.
As for me, I'd vote no.
I struggle with this a great deal on my end because the data is primarily used for reporting only. While it's good to restrict, the end users who pay your salary may defy everything holy and still insist due to lack of understanding the reproductions of their actions.
I get around this now by simply providing reporting data marts for the business case. The only issue is the bottleneck of creating a the data mart, but at least it's separate, read only and totally secure for only the end users for that specific business case.
One of the dangers of letting end users create their own reports is that they will, for example end user A goes in and sets up a report doesn't get the results expected and tweaks his query until he does get the results he wants but user B goes in and sets up a query for the same reason but doesn't tweak it. Now they both show up to a meeting with conflicting data and it's the DBA's problem to fix not their's.

That's only a danger if you allow it. If you're going to give the end user some ownership in the pipeline, they have to be held responsible for the issues the crop up with their end results.

To allow it to default all to you as the database professional is to take all the risk in allowing others work in the environment. Why you would allow that to happen is beyond me. I don't.

What users that can query the data are vetted by myself. We all work together. If issues crop up, we all still work together, but they do share the responsibility in both identifying and fixing issues they may have caused either with the query or the design of said report.

Otherwise, I would rip the right out their hands.