Do you allow developer to access Production Server

Question

Do you allow developer to access Production Server

Allen Cui-55137

SSC Guru

Points: 51650
More actions
September 10, 2003 at 10:02 am

#101659

Do you allow developers to access Production databases with QA or other tools? If you are, why? What kind of permissions do you grant to them?

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

mybid2003 SSCertifiable Points: 6620 More actions · Answer 1

I would prefer Prod. Support Personnel only will have READ-ONLY access to production database. For this on SQL 6.5 I have ProdSupport Group, which will have all SELECT on all the tables. NO EXECUTE. For 7.0/2000 I give DB_datareader role.

For some projects, if prod. support and dev support being handled by joint, then I give them SELECT ONLY on prod databases.

But in general, I don't feel comfortable giving Prod access to developers. Because they can upload the data into dev with SELECT on PROD and FULL access on DEV.

.

mybid2003 SSCertifiable Points: 6620 More actions · Answer 2

Some projects, they have different groups like Dev Support and Prod Support. In this case, I Don't give access to developers. That will be very very rare. I grant SELECT to them as an exception for few days.

.

Steven. SSCrazy Eights Points: 9806 More actions · Answer 3

For application support they sometimes get datareader/datawriter and the odd stored procedure.

No ddl, dbo or admin rights if I can help it.

Steven

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

quote:
But in general, I don't feel comfortable giving Prod access to developers

mdamera,

Agreed with you. They may create complex query with joins that could protentially impact the server's performance.

quote:
For application support they sometimes get datareader/datawriter and the odd stored procedure

Steven,

For those support person, I don't give them 'wirte' permission to the database and do not allow them to add stored procedures into databases. It could be protential risk to change/overwrite existing sps.

Edited by - allen_cui on 09/10/2003 1:38:22 PM

Andy Warren SSC Guru Points: 119902 More actions · Answer 5

I think it depends on the environment. There are quite a few cases where I work where 1-2 developers have the ability to make changes. Eases the bottleneck, but you definitely have to have buy in from above in case they do something wrong. In practice it's been rare that they break anything. Still not optimal! No reason not to have a test environment.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

philcart SSC-Forever Points: 47794 More actions · Answer 6

Generally, I don't allow developer access to production at all. If they want to see how something works in production they can organise a session with me.

If they're bug fixing it stills goes through development and testing phases, only when it's passed testing does it get anywhere near production.

At a past employer I successfully got a beefy developement server out of them. We ran multiple instances of SQL 2000. One instance served as Development, the other served as Test. Won huge kudos with management because we were able to implement a full development cycle and saved $$'s on buying seperate servers.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 7

In general NO, sometimes SELECT, but that's it. I guess Execute for Sprocs. NOTHING ELSE.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

http://www.dkranch.net

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 8

Interesting that you should start this discussion as I am working to eliminate this in my environments as I type. The big complaint is that they will not be able to troubleshoot production problems once I do this so I am considering a core "Troubleshooting" group that will have select privileges only. Should be fun.

Ultimately I am opposed to development having even read access on production. Maybe I'm too harsh...

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 9

There was a previous discussion on the dba / developer and it got a bit heated.

In principal I'll go with Andy Warren answer except for the test database part. For a bigger 24 x 7 database a test / development system is an absolute must.

Crispin Proctor SSCoach Points: 18161 More actions · Answer 10

I would give 1 or 2 more senior developers access but certainly restrict most people.

Becomes to easy for people to screw things up and just keep quite.

I have 9 enviroments ((Dev, SIT and UAT) * 2 phases) and production.

Two of us have access to SIT and UAT and only I have access to Prod.

While this does cause a bit of a bottle neck it also helps control mistakes made on the two important DB's

Cheers,

Crispin

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

ckempste SSCoach Points: 17983 More actions · Answer 11

NEVER. Simple as that.

Worst case is an urgent hotfix or some crazy windows issue, but never direct code changes or "playing" around.

Chris Kempster

http://www.chriskempster.com

Author of "SQL Server 2k for the Oracle DBA"

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 12

Why even SELECT may be bad...

We have an app where the developers have SELECT rights against all the tables. (DBAs lost that battle.) A few days ago a developer put together some monster of a query that ran TempDB out of log space. The developer proceeded to run the query not just once but eight times, each time receiving the same error. In production. Having never tested the horrendously bad query in any of the four other environments that have been created for them.

Bah! And then the DBAs had to track the guy down and ask, "Did you test?" and "Why not?"

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 13

quote:
NEVER. Simple as that.

Agreed with that!

Would certainly be a great issue at the security audit.

Our public accountants doing IT audits on an annual basis. Permissions, access and the whole stuff are always an area of interest to them

I know, my bosses (CFO and CEO...) would definitely not want anybody outside a very selective circle to gain a deeper insight into my financial transactions

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

fyk Old Hand Points: 355 More actions · Answer 14

Never. Because we have a DB with private information (privacy area designed by law). For testing we backup production DB, restore in stndl. server and run an application that mixing data. And this changed DB is restored on srv for developers.

****************************************

1: The question is ... "What is a MahnaMahna"?

2: The question is ... "Who cares?"

****************************************

****************************************
1: The question is ... "What is a MahnaMahna"?
2: The question is ... "Who cares?"
****************************************