For IAAS or "on-prem" SQL Server installations

For developing database changes on SQL Servers installed in traditional instances, whether they be installed on a laptops, on "bare metal" hardware, or hosted on Virtual Machines (whether in the cloud or on-prem), we recommend that developers have sysadmin permissions. 

This high level of permissions enables developers to:

• Code solutions for objects outside the database when necessary. This can include:
  • Managing or refactoring views or other objects which have dependencies across databases
  • Creating scripts to validate or manage elements outside the user database such as SQL Agent jobs
• Help develop automation solutions to refresh and manage development environments
• Use techniques for troubleshooting and debugging that require higher permissions, such as traces 

Addressing organizational concerns

Some organizations have concerns that high permissions for developers might create risks regarding sensitive data, damage to environments, or introduction of unwanted code that crosses database boundaries. 

In reality, concerns for the sensitivity of data also apply in lower-permission environments. As long as a user has read access to data, if that data contains sensitive information then there is risk of a data breach. 

To mitigate concerns while empowering developers to work effectively, organizations may:

• Implement data masking to provide safe datasets for development
• Use SAN snapshot technologies or database clones to provide on-demand creation of development database from these safe datasets
• Use static code analysis and custom rules in automated pipelines to ensure that code adheres to organizational standards

For PAAS SQL Server Databases

For hosted database environments, such as Azure SQL Database, Amazon RDS, or Google Cloud SQL database, we recommend that developers have db_owner permissions for a development database.

In addition, each developer needs db_owner permissions for a shadow database.