How to control access security in database Our database's security became a problem recently. Here is what we have:

Several basic roles -


read_only (select, view definition)
read_write (insert/update/deleted)
executor (execute SP/function)
ddl_admin (alter/create table/view/SP/function/trigger/almost any thing, it's a risk role and we are going to discard it)
several ssis roles for ssis jobs
etc


Those roles are in every database and in all environments (DEV/QA/STG/PROD).

We also have a bunch of user groups such as tech_leads, QA, developers (based on different projects), reporting, BA and so on.

We grant those users different roles on different environments. Usually people have more privileges on DEV/QA than STG/PROD. We almost only give regular user read only in production databases.

However, there were many occasions that users come to ask specific permissions on different servers. For example, the tech_leads may ask for server control on DEV/QA. Or one developer team ask for read_write permission on STG temporarily for trouble shooting.

After a long time, we got a massive database security.

Now our DBAs trying to think of some better ways to re-regulate the users and roles. We got advised that creating some new roles such tech_leads role, QA_readonly role, QA_leads role, developer role, and granting those roles to relevant groups.

The interesting thing here is, in same environment, say QA environment, the same role will have same permissions across all databases. But the role on PROD environment will be different than QA, meaning has much less permissions on PROD. Every time we modify the role in one database, we have to modify the role on all databases in that environment (we will have scripts for this of course).

I can't tell if the advise is good for long term yet. But another opinion would be keeping most of our basic roles, splitting some of the existing roles and create more tiny roles. For example, removing ddl_admin, and add a role to create SP/trigger/function, a role to create table/view, a role to alter, and a role to create synonym. This way, all of our roles will be static across all databases, and all environments. 

I want to know how you guys control your database security? And can you give us any suggestions on what I talked above? Thanks very much.
A common practice in my experience has been that if a user needs elevated access to a higher environment, you restore a copy to the level which they have access to. This ensures that you can keep similar security across most environments and mitigates privacy and security concerns as well as the creation of new issues. 

For example, if tech_leads need to troubleshoot an issue in STG, restore the staging level database into a dev environment (in parallel or overwriting the previous version). That way they can safely test fixes and then let that code be promoted through change management upwards through the environments. 

There will still probably be one-off occasions that arise, but that can be handled manually as they are ideally few and far between. Making all those extra roles sounds like more of a nightmare to keep track of and might create new issues.