Allen Kinsel - SQL DBA

When creating a new application, after going through the entire business analysis & requirements gathering process, normally you wind up with a datamodel that includes many tables and relationships.  By this time, depending on the size of the datamodel/system there has been considerable amounts of time invested on all sides.  We need a way of preserving this investment of time while still allowing developers to do their thing!

Deploy

Most shops have policies in place for what level of access developers can have in each environment.  In many places I've seen, developers are allowed DBO access in development, and some lesser access in the higher environments (read only usually).

After you've deployed the datamodel to the physical database in a development environment, before you grant the developer group dbo access consider all of the time/effort that has been spent making the datamodel what it is.  In order to allow the developers to do their jobs but not allow them to modify the actual table/schema layout you can grant a combinations of privileges.

Grant Alter Schema on the schemas where the developers will need to modify database objects (for instance stored procedures and functions)

Grant db_datareader --to allow read access

Grant db_datawriter --to allow write access

Grant Create Procedure, Function, Default, Etc  -- Allow developers to do whatever you are comfortable with

Deny Create Table in the database --This restricts all Table based DDL

Optional** Deny Create View, Function, Default,  in the database -- Restrict any create/alter permissions as needed. 

Important** Alter Schema permissions will allow Alter of ANY object type in the schema that you havent explicitly used a Deny on

Principle of least privilege

This method has proven effective to allow developers to write Stored procs, Functions & Views while still keeping the actual datamodel (tables and relationships usually) in pristine shape.   You could also mix and match your own grants/denys on certain object types to allow for unlimited configuration without granting the almighty DBO.  Yes, you might say that I'm a paranoid DBA who restricts permissions even in DEV!  Of course my great developers would never change a modeled database thereby forcing my hand into figuring out this lockdown of privileges