SQL Server Performance Forum – Threads Archive
What access for developers on SQL servers?
Constant battle this one [<img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ />]<br /><br />Dev:<br /><br />Do as they please – DBO of their databases (but not each others) but not server admin. <br /><br />Test:<br /><br />On test I say no access for developers other than read.Period<br />Simply because there should be no ‘tweaking’ that hasn’t gone through the proper release procedures<br /><br />Live:<br /><br />They say: We need full dbo access so we can fix ‘on-the-fly’ as the DBA might not always be around.<br /><br />I say: Full access to all objects but not DB_owner – i.e use a custom database role that can read/write but they can’t say drop the datbase or do restores.<br /><br />What do you do? Is it the norm for developers to have DB_owner access to databases on live?
I never gave full authority for Developers on the Production system, rather controlled with FIXED database roles using DB_DATAWRITER & DB_DATAREADER. Restrict to sysadmins-only access to stored procedures and extended stored procedures that you believe could pose a threat. There are quite a few of them, and this could take some time. Be careful not to do this on a production server first. Test on a development machine so you don#%92t break any functionality. Make sure roles at the server and database levels are only assigned to the users who need them.
Frequently check group or role memberships and make sure to assign permissions by group so your auditing tasks can be simplified. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
In my oppinion: never in the ******* life.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
… it is a constant battle if you allowed it to be….<br /><br />You have to set the rules and start with whos responsibility it is to ensure performance and make sure database and its data is fully recovered. If development team wants to say it is their responsibility in Dev – more power to them, they can have dbo (just do not come to me running when it breaks).<br /><br />Test – I share with QA team, development has no access to it. Once the product/patch/procedure is released to QA, everything else should be handled via QA procedures (change control requets, proper builds with full instructions). Test/Staging environment is not for development! Code must be unit tested _before_ it gets to QA.<br /><br />Prod/Live – they have no access to. Have been burned on way too many occasions when developers have access to Prod. May be the head of the development team , as exception, with read only access and strong message that if I detect any code coming from this account locking my production operations and run without WITH (NOLOCK) statements hisaccess would be revoked. <br /> Any stop that is past its initial startup face (more than few IT people) should avoid doing anything on-the-fly.<br /><br />and as for "We need full dbo access so we can fix ‘on-the-fly’ as the DBA might not always be around." – get more DBAs <img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ /> <br /><br />simas <br />
On a test system they could be whatever they want. However on a production system at most data_reader. Anything else would have serious implications on security audits. ———————–
–Frank
http://www.insidesql.de
———————–
I agree with your first post — Dev: dbo in databases they are developing, NOT system adminstration though. Test: Read-only or normal user access if required. Live: Read-only or normal user access if required. Tom Pullen
DBA, Oxfam GB
We use 4 environments… Dev: sa equivalent rights (to put development tools against) DevTest: sa equivalent rights (for their own beta test releases…) PreProduction: read-only or normal user access Production: read-only or normal user access but then our development group is fairly small so sa equivalents on dev/devtest servers is not a huge worry for us Cheers
Twan
hey, what about using SQL*Profiler? This is a serious issue when it comes to traces they (Developers) wanna keep track of …. Remember you only can use SQL*Profiler when you are in the sysadmin role list … and remember that its only YOU (the DBA) who should have those permissions… I had a bad headache when I thought it was fair to allow them build up their traces but on the other hand had this restriction … any of you have experienced that trouble?
]]>