We have a web server running web application and SQL Server.

Our developers have access to that server with Admin privilege and regularly upgrades the applications and add new stored procedures.

However, the database in it contains sensitive information which we do not want them to see, or even download.

Question: What is the best possible arrangement to prevent developers from accessing sensitive data?


You could do the following:

  1. create a database role on the specified database, and explicitly deny all necessary rights on the specified tables/objects in the database. Let's call the new role dev_denied
  2. create a new account for the developer on the server, if they aren't using their own login already. Besides the public role under the Server Roles, don't grant them any other roles. This will ensure that they cannot update their own database privileges, through their server privileges.
  3. add the developer account to the db_owner and the dev_denied role of the database.

This will provide your developer with admin rights to the database, but limit his access to the specified objects explicitly denied to him through the new role.

You will need to test this in your environment thoroughly. You can fine tune the access you want to grant by creating a specific database role for the developer and granting him the necessary privileges from there, without granting the db_owner role, which is much more secure. Above steps are the fastest way to your goal.

| improve this answer | |

Revoke the developer's access to the production system.

When an upgrade/update is needed, the developers provide a package and/or a script and then the authorized technician will perform the required operations.

The mandatory requirement for this solution to work is a meaningful test environment (read: test env do must be in sync with production but the data)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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