System test Database user management

Question

We are in development stage and to be able to give the developers an integration test environment, we need to give them access to the database, they can share and test their code in the system against.

The application is written in .net (MVC 4) and we are using SQL server 2008 r2 for database. We have restored the database they need to conect on the server, but I'm not quite sure what is the best approach in user management for this case?

Do we need to create an account for each developers, or it would be easier to create a generic account and give them this user/password to use? What would be the advantages and disadvantages of each option?

For example, If more than one person uses an account, would it be possible to differentiate who did what?

Also if we want to go with creating a generic account, is it better to create a SQL authentication account or windows authentic one?

Answer 1

I would recommend the following approach:

a) Each developer has their own database that they can work with, try out different things, destroy and restore at will independent of the other team members.

b) Have a shared database instance for integration testing so that the whole team can see the shared progress at any one time

c) Use windows authentication to speed up the connection process, and the devs do not have to remember new passwords, and easier to track and audit

Of course all this depends on the size of the database and what resources are available on the test server.