Last week, one of our developers accidentally deleted some data, thinking that he was connected to his local machine. The supervisor of our developers asked me to lock down accounts, something that I have wanted to do for a long time.
My first pass was to change all of developers to have a db_datareader role for all databases. While that has definitely worked to prevent deletions, it is too restrictive. My developers can't review a sproc, get to views, functions, etc.
So, while I have met the original request, there has got to be a way to set up a middle ground that allows them better access to the databases.
I am leaning toward creating a role for each database that grants additional rights to the developers, but I am wondering if someone else has been through this and how you addressed the problem.