1

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.

Community
  • 1
  • 1
Jeff
  • 12,555
  • 5
  • 33
  • 60
  • possible duplicate of [How to handle users and logins in Visual Studio Database Project?](http://stackoverflow.com/questions/16427719/how-to-handle-users-and-logins-in-visual-studio-database-project) – John Saunders Nov 19 '14 at 01:53
  • @JohnSaunders that question was for 2008; this is for 2012. The fact that the answer is the same is irrelevant. As mentioned in my question... – Jeff Nov 19 '14 at 01:54
  • Well, no, it's not irrelevant. It's the same answer because it's an area where SSDT/Database Projects has not changed since then. We don't need a new question for every different version of SSDT until Microsoft addresses this deficiency. – John Saunders Nov 19 '14 at 01:56
  • 1
    I don't see why. I came to the site, saw that answer and thought "Hmm, that is specifically for 2008, as per the tags. Is the same true of 2012?" Possibly that question should be reworked so it's a general question (remove the tag), but as it currently stands they are not duplicates - the answer to that question did not answer my question. – Jeff Nov 19 '14 at 02:00

1 Answers1

3

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.

Peter Schott
  • 4,521
  • 21
  • 30
  • Cool, so not much has changed since you answered that other question then :( thanks for the answer anyway. – Jeff Nov 19 '14 at 01:43
  • Sadly, not much at all. Apparently managing users/logins is now easier with SSDT if they're consistent in all environments, but not if they're different. However, I did try to combine all of my experiences into a blog post to make it a little easier to put together. I also tweaked the scripts a bit along the way. – Peter Schott Nov 19 '14 at 11:50