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.

2 Answers 2


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.

  • Thanks John. I think restoring a prod database copy to dev for testing is a good idea. However, we also have many huge reporting databases, and people may test them as well. How do you deal with the situation when people try to have access to giant reporting production databases?
    – Joann.B
    Nov 19, 2014 at 17:06
  • You can also replicate specific tables and stored procs into a dev environment, then BCP data into it from prod or generate sample data that fits the needed criteria to troubleshoot/test. If this isn't an option due to complexity/size then perhaps setting up a breakglass procedure with expiring passwords & audit tracking may be appropriate. Nov 19, 2014 at 17:27

@Joanna.B Allowing access is a tricky and well planned strategy. Ultimately DBA's are responsible to allow access and maintaining of databases and mainly in STG and PRODUCTION environment. You do not want to allow any unnecessary access into STG and PROD environment because ultimately you DBA's will be responsible for any changes.

First of all if you allow access to STG or PROD to make you receive proper authentication from your manager or requesting person in written.

and coming back to question: No one should be allowed access in PROD. As @John M mentioned we use policy of restoring Databases to DEV and UAT environment. Developers and other related departments have access to that environment as they are needed. We allow then further access as per ticketing and remove access after troubleshooting is completed or if it is permanent than again get proper approval.

sometimes we create additional users and allow them access to specific database and drop users after testing, troubleshooting is completed as it do not allow access to instance level but just to database level.

Hope, this helps you in deciding further.

Good Luck!!

  • Thanks for your comments! Our problem is that people asked for additional permissions all the time but never inform us when they are done. Think we should keep better track of the changes and revoke them anyway after a certain amount of time.
    – Joann.B
    Nov 19, 2014 at 17:03
  • If your environment is not too big than then you can manually followup and remove. We have ticketing system where we keep track of it. Or as a DBA make a monthly task of checking and reaching out to people and make sure their purpose of access is done and you can remove access to not required instances.
    – Im88
    Nov 19, 2014 at 17:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.