December 10, 2002 at 10:17 am
Our sql developers need the ability to read, write, modify, and execute stored procs in development databases (each group has its own dev server). Should I just make them all dbo's of all the databases or give them permissions (db_datareader, db_datawriter, db_ddladmin, and manually grant them EXECute for SP)?
TIA,
Bill
December 10, 2002 at 10:23 am
I like to not give them dbo, but it depends. If you have good change control and testing, then maybe it doesn't hurt.
Rather than grant what you suggested, create a developers role and give that the rights they need. They don't necessarily need dbo to do what you want.
Steve Jones
December 10, 2002 at 11:02 am
Like most open ended questions: It depends.
As noted, farming out responsibility can be a good thing. It offloads the app design tasks to the folks who know the app. Your DBAs can manage the data base and environment.
What we generally do is create 3 IDs for each application: an AppDBA ID, an AppDEV ID and an AppUser ID. The AppDBA can create tables, views, etc. The AppDev can create stored procs and views but not modify tables, indexes, etc (DBA stuff).
The AppUser can only EXECUTE stored procs. It has no access to any other data base objects. This has worked well for 5+ years.
December 10, 2002 at 11:24 am
However you did state this is a devleopement server. So as long as there is nothing mission critical or matter if they mess up then I would say it would be ok to make them a member of dbo and let them work without henderance.
If however there are things that they may not want others to see then create Roles in each DB and grant the appropriate roles to each role and add the users that need that role. As for DTS, any user can create a DTS package and save but the roles they are in will control what that can do in the DTS package with regards to DB access.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply