How should I handle users in database projects?

1.6k views Asked by At

We have two environments - a dev environment and a production environment.

We have a different group of users in the development environment than the production environment, but there doesn't seem to be a good way to represent this in the database project - whenever we deploy it will wipe out the existing users, and we can only have one set - either the production users or the development users.

I thought of including scripts that switch based on SQLCMD variables to create the appropriate users, but that seems pretty nasty.

I feel like I'm missing some part of the database project, as this seems like a situation that will come up pretty well everywhere ever. Is there capability in database projects to handle creating different sets of users/role memberships in different environments?

There is an existing question about 2008R2 which seems to indicate not, but I'm optimistic that maybe there's been some changes in 2012 to provide this functionality.

1

There are 1 answers

2
Peter Schott On BEST ANSWER

Sadly, this is one area in which SSDT lacks. You'll need to build out your permissions and users differently. I've written up the method we've used here (with props to Jamie Thomson for the idea): http://schottsql.blogspot.com/2013/05/ssdt-setting-different-permissions-per.html

Basically we build out the permissions in a series of scripts and apply them depending on the variable passed to the SQLPackage command. That also gives us some flexibility with other areas, such as not running certain scripts in those environments, but it is a pain to set up the first time.