Hi,
I have a development user that is semi proficient is simple sql, and getting better. this individual develops queries in the test environment where he has dbreader rights. once he is satisfied with the query he submits it to me, I run it in live db and copy and paste the results, which can be a number of tables, to excel and return it to him. to eliminate the requirement of my involvement and to allow Devo quicker access to results in response to data requests I am considering giving him dbreader access to the live database and am concerned that he will accidently write a query that eats all sql server resources or starts locking processes.
Has anyone dealt with this situation and if yes how, and were there any gotchas?
===========================
Mendy Sudranski
IT Manager
Manhattan Theatre Club
311 West 43rd Street - 8th Fl
New York, NY 10036
Phone: (212) 399-3000 ext. 4105
msudranski@mtc-nyc.org
Mendy,
I am a Development person in almost the same position as the person you’re talking about and I think we have a good setup for me here at the museum. I have full admin access in a Dev database and when I am satisfied with a new report, I simply tell our IT department and the DBAs move everything into Live for me. This way, I’m not able to mess up anything in Live (and therefore cause major problems) but I still have enough access to write queries and edit reports. (We actually more everything into our main Dev database first, but then it gets moved into Live.)
Let me know if you have any other questions and I can pass you onto our DBAs who can give you more detail on how they set up the extra Dev database.
Hope this helps,
Laine
--
Laine Kyllonen
Database Application Specialist
Science Museum of Minnesota
(651) 221-2515
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Mendy SudranskiSent: Thursday, June 06, 2013 10:55 AMTo: lkyllonen@smm.orgSubject: [Tessitura Technical Forum] give user dbreader rights to live tess db
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
NOTE: This message was trained as non-spam. If this is wrong, please correct the training as soon as possible. SpamNot spamForget previous vote
Hi Mendy,
I am our Tessitura administrator here at the Public and I have given live db reader access to one of our Development staff for the same reasons.
I also had the same concerns as yourself. To get around this, whenever she executes any queries, I instructed her to put the following statement at the top of her query:
set transaction isolation level read uncommitted
This is the same as putting ‘nolock’ after every table and ensures that whatever she runs, it’s not going to bring the database to a halt. Haven’t had any issues as yet!
Natasha
This is just what I’m looking for. I’m going to expand on your set statement to
SET DEADLOCK_PRIORITY low
SET LOCK_TIMEOUT 1800
SET QUERY_GOVERNOR_COST_LIMIT 600
And adjust as necessary but I think these should do it.
using QUERY_GOVERNOR_COST_LIMIT without activating and maintaining sql statistics requires you to use a higher seconds count since sql server's query run time estimates are way off.