January 3, 2007 at 7:33 am
I'm not sure where this topic should be discussed, but I'd like some feedback on an issue we've been having at our company--which is the distribution of Query Analyzer. We have a certain production database that everyone (outside of IT) wants to query so end-users are requesting to have Query Analyzer to analyze data. I have explained the issues that could arise on the database when end-users write bad queries to a manager and that it has been basically an unqritten rule not to install it on users' pc's. We have a log-shipped database that is used for reporting, but the users want to query the live database for up-to-the minute information. (Log shipping is only done once nightly. This cannot change due to various reasons so is un-negotiable.)
Another potential problem is that many times users have accounts on other database servers (we have 30 SQL Servers and over 90 databases so this possibility is high). I can imagine the havoc that could be caused just by distributing QA. Does anyone else have this problem? Any opinions? I'd appreciate the input.
January 3, 2007 at 8:24 am
Are these end users?
I'd avoid giving this to end users. Reporting Services, with the Report Builder, is supposed to address a lot of these concerns.
January 3, 2007 at 8:40 am
This particular server is SQL 2000 and we have Reports on the secondary database, but the end-users do not want reports. These users are analysts and a couple of them want QA to query other databases as well. We are also looking at Crystal Business Intelligence for its analytic capabilities, but that would not be in the near future.
It seems that everyone who knows a little SQL thinks they can analyze data. Some table relationships are very complex, and I doubt they'd be getting the results they want anyway. So far, the supervisor is agreeing with me not to distribute it, but I feel that it's just a matter of time....
January 3, 2007 at 8:49 am
Hopefully you are:
If these are true, then you can make user accounts have only select access to your database tables, and do not let them know the application account logons. For even greater security and easier user access, create a few views that perform the complex table joins, and grant select rights to those views instead of the tables.
Possibly another option would be to create an empty database, with only views to the production database. You still need to give them rights to production database, but hopefully they will not look there if they can get what they need from the views.
Hope this helps
Mark
January 3, 2007 at 10:27 am
I can't set the system to use windows authentication only because of the 3rd party applications. Creating views would not solve the performance problem because the tables are huge and the database is very busy 24/7. This also would not address the fact that the users may have access to other databases or that if you give QA to one person you cannot refuse another. There seems to be a fine line between ethics and common sense here 🙂
Thanks for the ideas though. I'm open to any and all suggestions.
January 3, 2007 at 12:52 pm
Becareful when you are giving the access through QA to production servers even if it db_reader access...
If someone runs select * from table which millions of rows without NOLOCK or setting the ISOLATION LEVEL to READ_UNCOMMITTED ... table may get locked and other users can't access the table...
Make sure you explain the users how to run the queries..
1. Deny access db_datawriter unless it is neccessary...
2. Ask the users to use where clause...
3. Ask the users to use NOLOCK hint...
...
MohammedU
Microsoft SQL Server MVP
January 4, 2007 at 6:39 am
I thought of those things too, but I do not intend on "training" users as it would enable the snowball effect (plus I don't have that kind of time). I cannot restrict the db_datawriter role because the real developers need it. I guess I'm looking for some confirmation for my stand on this issue. Since I am the only DBA and there are hundreds of users, I'm very outnumbered so I may end up losing the battle.
January 4, 2007 at 6:54 am
Thi solution will surely work for you just try this.
1. Give the users the necessary permissions
2. create a DSN in thier machine with proper database access
3. ask the user to use excel (option get external data)
4. use new database query and ask them to query from there
by this way u are not giving them QA but still u make them to work as if the work in QA.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
January 4, 2007 at 7:54 am
Been there, done that. Users don't want to use Access or Excel because they are too clumsy and slow.
"Luckily" someone updated an entire table wrong yesterday so I was able to use the example of what can be done through QA. This only happened because the end-user "inherited" QA and had a dbo login to "babysit" a database while the owner was on vacation.
I may have my substantiation not distribute QA after all....
Many thanks for all comments. I'll see what happens from here 🙂
January 4, 2007 at 12:55 pm
Did you discuss/warn this with your manager/cto?
I know it is hard to convince managers some times...
Good luck Linda...
MohammedU
Microsoft SQL Server MVP
January 4, 2007 at 1:13 pm
Actually, yes, but he is new and getting pressure from those who have been here much longer. So far he's in my corner.... I'll take all the luck I can get--thanks.
January 10, 2007 at 11:23 am
In case anyone is interested, my issue is resolved. I was talking to my previous supervisor (who is now the director of IT) and he asked me how things were going 🙂 I happened to mention that quite a few end-users want Query Analyzer for various systems and he immediately understood my dilemma. The end result is that Query Analyzer stays in the IT department.
It always helps to have a heavy-weight in your corner. End of story 🙂
January 11, 2007 at 11:45 pm
great that u had a heavy-weight in your corner to support you. mostly end-users don't accept whats said in the security document. and its hard to convince them. the g8 news is somehow u have avoided the dangerous situation of installing QA in every end user machine.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply