1

I'm doing a small pilot project trying to implement Sql Server Data Tools sqlproj projects in order to better bring our databases under source control. In my organization, we have separate no-trust domains for test environments of various purposes, so these domains of course have their own isolated active directory accounts.

The documentation is still somewhat sparse and I don't really know where to go for more information on this toolset, especially considering the extraordinary amount of churn in Visual Studio's history of database assets.

So far, the only idea I've really had would be to make separate sqlproj projects specifically for the security objects each separate domain, separate from the other schema objects. My hope is that somehow I can tie my actual database schema to those at deploy time and also to somehow switch which security project I'm using in the build. I have no idea if that's feasible though.

Has anyone that uses Visual Studio sqlproj projects had to deal with this? Is there a best practice for this kind of thing?

bwerks
  • 8,651
  • 14
  • 68
  • 100

1 Answers1

2

If you have different settings for each environment then the easiest is to either leave them out and not delete them when you deploy or to have a post deploy script that sets them up manually.

Normally for handling different configurations I would suggest using sql cmd variables (on the properties of the project there is a page for setting these up) but when you create a login you cannot use a variable to create it so that falls over!

There is an example on how to setup a post deploy wrapper for just this case:

http://schottsql.blogspot.co.uk/2013/05/ssdt-setting-different-permissions-per.html

Good luck with ssdt, there are some strange quirks but it enables so much!

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Ed Elliott
  • 6,666
  • 17
  • 32
  • 1
    Thanks for the referral, Ed. I was about to post this. :) Use Publish Profiles to make this a whole lot easier. – Peter Schott Oct 13 '14 at 21:15
  • @PeterSchott Thanks for writing this article! I'm attempting to work through these steps, but generating the list of logins is a bit of a challenge because sp_help_revlogin applies only up to SQL Server 2005 and I'm on 2008 R2. Specifically the master..sysxlogins object doesn't appear to exist anymore. As well, I haven't been able to find an update for this KB. Was this solution written using a 2005 DB? – bwerks Oct 14 '14 at 06:34
  • 2
    you should be able to use this sp_help_revlogin it applies to sql up to 2014: http://support2.microsoft.com/kb/918992 – Ed Elliott Oct 14 '14 at 20:02
  • What Eddie posted - and yes, I originally generated this from a 2005 server. Time to update my blog post. – Peter Schott Oct 14 '14 at 20:38