Setting up a shared DB for multiple users. I want to allow users permission to do anything they need but prevent deleting another users files. However, they do need to be able to delete their own files. Any ideas on how to accomplish this?
asked
Allow users permission to do anything they need but prevent deleting another users files.
We have a Sandbox DB schema that multiple developers will be utilizing for testing and development. I want to give them full access so they can create objects, tables, views, etc. Since this is a shared space, others can read from and update another developers tables. However, I want to prevent user A from accidentally deleting user Bs objects. Therefore, I only want the creator of a table to be able to drop a table. Is that clearer?
Before continuing, I would highly recommend that you spend a few minutes reading The unnecessary evil of the shared development database or, the even older, Overthrowing the Tyranny of the Shared Database just to illustrate how old this problem is and that a version control system is a well-established best practice in database development.
With that out of the way, if the developers need to share a common sandbox schema within a single database, you won't be able to manage this.
If the shared database itself is the sandbox but you can have multiple schemas, it's a bit convoluted, but doable. You can create a single schema for each developer that they own. This will allow your developers to create/alter/drop their own objects. Make sure they aren't in the db_owner database role so they can't create/alter/drop objects from other users' schemas. Finally, place each developer in the db_datareader and db_datawriter roles so they can read/write data to tables/views in the schemas of other developers. You probably won't want this schema separation to continue through development, test and production, so it would be best if you didn't implement this at all at the development level.
Ideally, each user would have their own sandbox database and you would have the database definition scripts in source control then deploy all changes from there so developers can pull the latest whenever they need it and you can push whatever they check in to other environments. The important thing is that the source control version of the database is canonical and the only to get your changes into the test/production database is by placing your changes into the version control system and they only way to get the current version of the database is from the same system.
Much appreciated @KenJ and I completely agree with individual development environments (all I've ever used previously). Unfortunately, for the time being, I'm stuck with this setup for now.
19 People are following this question.
