Should developers be given permission to query (SELECT / read only) production databases?  The previous place I worked, the development team had the db_datareader role; where I work now the development team can't even connect to the production instance.

One of the test instances is a copy of production restored from a production backup once a week, so there aren't any problems with developers actually seeing the data.

What good reasons are there for not allowing developers to query production (except for simply not wanting them to have access to read sensitive data)?

It really depends on whether the developer has any support responsibilities.  If they are on the hook for third line support then they will probably need to look at the production database to do this.

Generally it's a bad idea to do anything on a production server unless it's really necessary to do it there.

For most development purposes, mirrors or snapshots of the production database will be adequate, and probably better than the live production database.  If you are doing anything involving integration then you will want stable database environments where you can control what's in them.  Anything involving reconciliation will also need the ability to look at a controlled point in time.

If the problem is that you don't have production mirror environments or any means to put a copy of production data somewhere for your developers then this is a somewhat different question.  In that case your developers really need at least one mirror environment.  If you can't see what the problem is in the data then it's kind of hard to troubleshoot it.
