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

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:
>> 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
>[quoted text clipped - 10 lines]
>> 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
This is not against production. And these are not developers these are
accountants and other users. Who have no sql training what-so-ever.|||> This is not against production. And these are not developers these are
> accountants and other users. Who have no sql training what-so-ever.
You mean you hand over you military fighter jet to somebody who never piloted an airplane? I
wouldn't do that. Analyze what information they need and use some reporting tool to write reports
for that. Or, at the least, analyze what information they need, create views for them and they can
use some "power-user" tool (like SQL Server's Report Builder) against those views.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mmalatek" <u40725@.uwe> wrote in message news:7e5b507a82324@.uwe...
> Rick Sawtell wrote:
>> 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
>>[quoted text clipped - 10 lines]
>> 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
> 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:
>> 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
>>[quoted text clipped - 10 lines]
>> 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
> 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:
>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.
>> 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
>[quoted text clipped - 10 lines]
>> 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.
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:
>>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.
>> 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
>>[quoted text clipped - 10 lines]
>> 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.
> 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, Manfredsql

enabled/disabled protocols for an msde instance

Hi folks,
Wondering if someone could help me out with an msde/windows sp2 problem.
Upon installing sp2, the network protocols for my msde instance become
disabled: Sm, Np, Tcp, Via. Is there a command line syntax I can use to
re-enable these network protocols? Or is there a windows interface to enable
them? basically my msde instance is now standalone, no other workstations
can talk to that sql instance. I can't install sp3 for msde because the
manufcaturer used merge modules (bleh!).
Any help would be greatly appreciated.
Thanks,
S. Purkiss
sheldon@.REMOVETHISthedoctorcompany.com
hi,
"S Purkiss" <shel@.shel.com> ha scritto nel messaggio
news:w7idnY9fmfsFnOLcRVn-rA@.rogers.com
> Hi folks,
> Wondering if someone could help me out with an msde/windows sp2
> problem.
> Upon installing sp2, the network protocols for my msde instance become
> disabled: Sm, Np, Tcp, Via. Is there a command line syntax I can
> use to re-enable these network protocols? Or is there a windows
> interface to enable them? basically my msde instance is now
> standalone, no other workstations can talk to that sql instance. I
> can't install sp3 for msde because the manufcaturer used merge
> modules (bleh!).
> Any help would be greatly appreciated.
please run Server Network Utility (svrnetcn.exe) and re-enable the desired
protocol...
with MSDE sp2 level you are exposed at Slammer/Saphire troubles, which have
ben solved with service pack 3 - 3/a..
lot of security problems has been addressed to with that sp, so please
"force" your ISV to ship sp3, as it's been out for quite 1 year, and it
should already had enougth time to test and ship it...
my $0.02
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Enable Common Criteria Compliance

SQL 2005, SP2
In the Security section of the Server Properties dialog in SSMS, there is a
check option for "Enable Common Criteria Compliance". In the online help,
this position is listed as the "Allow Direct Updates to System Tables"
option that has been around for a while.
Does anyone know if the "Enable Common Criteria Compliance" is a one for one
replacement for the "Allow Direct Updates to System Tables" or does if have
different functionality? I did google and Microsoft web searches and
couldn't come up with any hits on the Enable Common Criteria option.
ThanksSorry for duplicate messages. I had an operator malfunction.
"Al" <zyck@.tconl.com> wrote in message
news:OhDQHm1VHHA.600@.TK2MSFTNGP05.phx.gbl...
> SQL 2005, SP2
> In the Security section of the Server Properties dialog in SSMS, there is
> a
> check option for "Enable Common Criteria Compliance". In the online help,
> this position is listed as the "Allow Direct Updates to System Tables"
> option that has been around for a while.
> Does anyone know if the "Enable Common Criteria Compliance" is a one for
> one
> replacement for the "Allow Direct Updates to System Tables" or does if
> have
> different functionality? I did google and Microsoft web searches and
> couldn't come up with any hits on the Enable Common Criteria option.
> Thanks
>
>|||Did you also update your Books Online (separate download)? I did, and searching for below gave me
three hits. It is *not* the same as the old "allow update", they probably only re-used the BOL URL
(or similar).
Enable Common Criteria Compliance
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Al" <zyck@.tconl.com> wrote in message news:OhDQHm1VHHA.600@.TK2MSFTNGP05.phx.gbl...
> SQL 2005, SP2
> In the Security section of the Server Properties dialog in SSMS, there is a
> check option for "Enable Common Criteria Compliance". In the online help,
> this position is listed as the "Allow Direct Updates to System Tables"
> option that has been around for a while.
> Does anyone know if the "Enable Common Criteria Compliance" is a one for one
> replacement for the "Allow Direct Updates to System Tables" or does if have
> different functionality? I did google and Microsoft web searches and
> couldn't come up with any hits on the Enable Common Criteria option.
> Thanks
>
>