End user query analyzer access

My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
backend for peoplesoft. Our end users are requesting sql query analyzer
access in order to run select queries. Peoplesoft does have a built in query
tool. I am against giving them query analyzer access because of the following
- The end users are not technical and I am concerned about their ability to
write correct queries
- A long running select can take down the system
- Escalation of security privleges
I am curious on other thoughts or if anyone knows of what microsofts
recommendation is on this. Or how this is handled in other shops.
"mmalatek" <u40725@.uwe> wrote in message news:7e5a365f773b0@.uwe...
> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
> backend for peoplesoft. Our end users are requesting sql query analyzer
> access in order to run select queries. Peoplesoft does have a built in
> query
> tool. I am against giving them query analyzer access because of the
> following
> - The end users are not technical and I am concerned about their ability
> to
> write correct queries
> - A long running select can take down the system
> - Escalation of security privleges
> I am curious on other thoughts or if anyone knows of what microsofts
> recommendation is on this. Or how this is handled in other shops.
>
From a security perspective. If they can already run the queries through
the PeopleSoft tool, then you could set them up with QA utilizing the same
permission sets.
As for the long running queries, again I point at the PS tool. They can
write poor queries there as well.
At my company, we have taken the time to train our PS developers to take
advantage of things like NOLOCK and TOP xxx when they run their queries.
In general we don't have issues with them tying up the systems.
Last point: Why do they need to run queries against production? They
should be running them against a development and/or a test environment.
Rick Sawtell
|||Rick Sawtell wrote:
>[quoted text clipped - 10 lines]
>From a security perspective. If they can already run the queries through
>the PeopleSoft tool, then you could set them up with QA utilizing the same
>permission sets.
>As for the long running queries, again I point at the PS tool. They can
>write poor queries there as well.
>At my company, we have taken the time to train our PS developers to take
>advantage of things like NOLOCK and TOP xxx when they run their queries.
>In general we don't have issues with them tying up the systems.
>Last point: Why do they need to run queries against production? They
>should be running them against a development and/or a test environment.
>Rick Sawtell
This is not against production. And these are not developers these are
accountants and other users. Who have no sql training what-so-ever.
|||Some people prefer to hand out Access or Excel over Query Analyzer for that
set of skill levels.
For what it is worth, I prefer to give them access to views rather than
directly to the tables. If either wise or necessary you can the do a couple
of things:
- Prepare views that do a basic set of common table joins, easing their work
and reducing the number of poorly designed joins.
- Make views that do not lock the tables so as to increase concurrency.
(Depending on the server level and the method used, this may raise other
problems. For both server versions read about 'isolation levels' and the
FROM statement's WITH option.
FWIW,
RLF
"mmalatek" <u40725@.uwe> wrote in message news:7e5b507a82324@.uwe...
> Rick Sawtell wrote:
> This is not against production. And these are not developers these are
> accountants and other users. Who have no sql training what-so-ever.
>
|||Consider, if budget and time permits, creating one or more Analysis Services
cubes and letting them play with those instead.
Possibly only appropriate if your end users are dealing with aggregated data
much of the time, but this is usually the case.
"mmalatek" <u40725@.uwe> wrote in message news:7e5a365f773b0@.uwe...
> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
> backend for peoplesoft. Our end users are requesting sql query analyzer
> access in order to run select queries. Peoplesoft does have a built in
> query
> tool. I am against giving them query analyzer access because of the
> following
> - The end users are not technical and I am concerned about their ability
> to
> write correct queries
> - A long running select can take down the system
> - Escalation of security privleges
> I am curious on other thoughts or if anyone knows of what microsofts
> recommendation is on this. Or how this is handled in other shops.
>
|||Will Alber wrote:[vbcol=seagreen]
>Consider, if budget and time permits, creating one or more Analysis Services
>cubes and letting them play with those instead.
>Possibly only appropriate if your end users are dealing with aggregated data
>much of the time, but this is usually the case.
>[quoted text clipped - 10 lines]
I appreciate everyones input. What i am trying to get at is I want to make
sure my reasoning is on track. I have already offered to create views but I
need supporting reasons other then end users scare me.
|||If you can create very simple views, and your users can cope with SQL, go
for it. Just ensure your database is fully locked down so no smart Alec
ends up DELETEing a whole bunch of records 'just because they could'!
I'd recommend keeping an eye on the performance. If this were a production
server, I'd never let users have access!
"mmalatek" <u40725@.uwe> wrote in message news:7e5c625b80010@.uwe...
> Will Alber wrote:
> I appreciate everyones input. What i am trying to get at is I want to make
> sure my reasoning is on track. I have already offered to create views but
> I
> need supporting reasons other then end users scare me.
>
|||If there are performance problems you can set up a query server by
replicating the data. That should be no problem.
One of my customers has a query database at the production server.
This query database contains only nolock-views onto the production
database. They use Access as a query tool.
Often I would appreciate if they would use Query Analyzer because in
Access it is much too easy to busy sql server AND network! They write
poor queries and they cannot rely on their results - but they wouldn't
invest in a relyable and fast querying tool.
There is not one solution. In Query Analyzer you experience problems
in handing out the data to Excel. In Excel and Access you experience
problems in writing good sql ... and no enduser will write pass
through queries in Access. The best way is a querying tool. If they
need to build queries on their own, they have to know that they are
developers - and they have to behave as. Perhaps could some skillfull
power-users build queries for others - that would help.
Bye and good luck, Manfred