March 23, 2009 at 11:51 am
I work for a small company where our programmers develop the databases themselves. ( I;m responsible for backups; perf tuning, encrpytion technology, imaging..)
1. The Dev's are generally dbo of the dev databases they work on.
This seems OK to me because they need that type of power when they are developing code.
Do you agree with me or not on that point?
2. To my surprise many of the same dev's are also dbo of production databases. I don't like that but sometimes they are called upon to troublshoot issues in their own code.
How would you handle this? Give them no acceess to prod databases unless needed in the event of a crisis?
3. Each database has a single "service" (non-user) account that accesses it. Instead of making it dbo (as was done in the past) I want to give that account read\write\execute permission to the database (as a start).
Please comment on this approach.
TIA,
barkingdog
P.S. I am asking these questions becaue I have no other "role model" to pattern the design after.
March 23, 2009 at 11:55 am
1.Devs as dbo for the dev database should be fine. Should have a test server/database where they are not dbo, and where they use the "regular" login, in order to make sure everything works correctly from that login.
2.Generally a bad idea, but sometimes you can't avoid it. They should be troubleshooting in dev/test, not in production.
3.Sounds good.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2009 at 1:42 pm
Hi
2.
You should consider how of the developers really need a dbo account on your production system. I'm also a developer and have a dbo account on our production system but I'm the only one of our project team.
It's very important that they usually do not use this accounts within their applications/processes to avoid that a non stable version connects to production system.
Greets
Flo
March 23, 2009 at 2:09 pm
Barkingdog (3/23/2009)
...3. Each database has a single "service" (non-user) account that accesses it. Instead of making it dbo (as was done in the past) I want to give that account read\write\execute permission to the database (as a start)...
The best way is to setup a single database role for the application login and grant permissions to that role, not directly to the login user. That way, when you change logins, you do not have to re-add permissions.
Do no add the application login role to any of the fixed DB roles. Instead, add permissions to individual objects as needed. Do not grant the application login role any permissions directly to tables or views, but limit all access to be done via stored procedures. This makes it easier to fine tune the permissions, and makes your application less sensitive to SQL injection attacks. It is always easier to lock down the database first and grant permissions as needed, than to try to tighten them up later.
Do no use the same login for access to development/QA and production databases. This helps to prevent dev/QA logins from connecting to production by accident.
Do not use SQL Server logins for the application login; insist that they use windows logins.
You can expect to hear a lot of whining from developers when you do this, so develop a thick skin.
March 23, 2009 at 3:24 pm
This last part is pretty important 🙂
You can expect to hear a lot of whining from developers when you do this, so develop a think skin.
* Noel
March 23, 2009 at 4:56 pm
1. If you've got a DBA on staff, there's really no need for developers to be dbo. We'd rather have our DBA staff control backups and security. Developers seem to do fine as members of db_datareader, db_datawriter, db_ddladmin, and, in some cases, db_securityadmin. We also use cross-database ownership chaining, which would be messed up by having different dbos for different databases.
2. I see no reason for a developer to ever be dbo of a production database. As GSquared said, they should do their troubleshooting in dev or test.
Greg
March 23, 2009 at 6:13 pm
Greg,
You wrote
>>> 2. I see no reason for a developer to ever be dbo of a production database. As GSquared said, they should do their troubleshooting in dev or test.
Is the idea that they could t-sql script their fixes and pass them on to the DBA for review and implementation in production?
Barkingdog
March 24, 2009 at 7:25 am
Barkingdog (3/23/2009)
Greg,You wrote
>>> 2. I see no reason for a developer to ever be dbo of a production database. As GSquared said, they should do their troubleshooting in dev or test.
Is the idea that they could t-sql script their fixes and pass them on to the DBA for review and implementation in production?
Barkingdog
That's a pretty standard set up.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 24, 2009 at 9:16 am
Yep. Everything that goes into production gets reviewed and implemented by a DBA in our shop.
Greg
March 24, 2009 at 7:33 pm
Thank to everyone for the good advice.
barkingdog
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply