Granting Permissions to Developers

Hi Everyone,

Were looking from some feedback, thoughts, comments, suggestions on a permissions issue in our SQL Server 2005 environment. We have a Development (stand-alone server) and Production (active\passive cluster). Both running SP2. We're looking for the best way to manage permissions for the developers without giving them dbo privileges.

As background information, here's a note / response from a developer outlining the permission they need.

--

"Our team needs enough permissions on DB database to:

Create and modify DB object such as tables, stored procedures, keys, triggers, views, ...

Be able to execute stored procedures and other basic DB objects

One of the issue I had when working on DatabaseA which was never resolved was that I could create tables, but had no rights to modify them once created.

I could modify them via scripts, but not via graphical interface which is a pain.

I think we should have full permissions to dev DB other than to drop DB, and modify system DB settings.

So that we can work within the created DB shell completely."

--

Previous to supporting the current SQL Server 2005 environment, the developers\app owners had db_owner for their databases. Definitely not a best practice as with 2005 db_owner is ablke to delete databases. Moving away from this, we thought we would scale them back and only grant them the following database roles:

db_datareader

db_datawriter

db_ddladmin

With these roles, they were able to create the tables, views, stored procedures, etc...... but problems arose when they tried to modify the objects they created as well as execute stored procedures. It's going to be a pain to grant permissions on each stored procedure. More of a mystery is allowing them to make design changes within Studio Manager. Given they can already do this using scripts but not the GUI.

So there is an issue with using GUI tools to design the tables.

Here's a thread we found that tries to explain the reason why they can't use the GUI to make design changes.

Even if you have CREATE TABLE permission, there are limitations to the modifications you can make. Remember, as you modify an existing table or design a new one, your work can induce attendant modifications in other tables. For example, if you change the data type of a foreign-key column, the corresponding column in the primary-key table will be automatically modified by the Visual Database Tools. If you do not own the primary-key table, and you are not logged in as the system administrator, database owner, or a user that is a member of the db_owner role, your modification will fail.

When we created the accounts, we assigned the default schema to be 'dbo'. This was thought to simplify administration of the objects. In SQL Server 2000, this was a best practice. Now, in SQL Server 2005, they changed everything with the user \ schema separation.

So we have a developer (devuser) - created with default schema (dbo). Any object that are being created are owned by dbo. (ie... dbo.table1, dbo.storedproc1, etc..) So dbo owns the objects, not the user.

Does anyone have any ideas? Do we create a new schema and assign permissions? Do we create a new fixed database role for the developers? I'm curious as to how others are assigning permissions to developers on development and production servers.

Thanks,

Rob

From my experience, the following is the most effective approach.

On the Dev server, Developers are in the dbo_owner role. They can create and/or destroy anything they want in that environment. They can freely experiment to solve their problems.

On the QA and Production servers, Developers are in the db_datareader and db_denydatawriter roles. They are not allowed any extraordinary levels of permissions in the QA and Production servers. They can ONLY see the data. If they need to alter data, they 'must' submit a (documented) request to the dba. They cannot alter any object, change any permissions, create or alter procedures, functions, views, etc., on the QA or Production servers.

And schema changes to the QA and Production servers are fully vetted with the DBA, and ONLY migrated to the QA and Productions server after extensive testing and sign-off by the QA team.

|||Thanks for the feedback. We'll pursue the same approach. Any other comments/ suggestion are appreciated.|||See this http://sqlserver-qa.net/blogs/tools/archive/2007/06/17/dba-role-in-question.aspx too.