How do I deploy a database into a different Active Directory domain?

• 
  Hi Nick,
  
  If I understand correctly this is happening because you have Windows users on your database schema that are not valid in the SQL Server instance that you are deploying to?
  
  Are you using a SQL Change Automation or SQL Source Control project?
  
  Kind Regards,

  Sergio R May 12, 2020 18:00

  - 0 +
• 
  Hi Sergio,
  
  Yes, our new dev/test environment is being built in it's own AD Domain.  This gives us problems in that the Windows users on the production database will be like Prod\JohnSmith and in the test database will be Test\JohnSmith.
  
  We are using SQL Change Automation projects.
  

  Nick_Foster May 13, 2020 08:00

  - 0 +
• 
  Hi Nick,
  
  We recommend that when developing databases that are going to be part of a DevOps process is to allocate permissions to roles only.
  We also recommend using a filter that excludes users from import.
  You can then keep environment specific users in the databases.
  Since you have already imported those users into your migration scripts, the best option now is to edit the migration scripts to remove them.
  
  I hope this helps,
  
  Kind Regards,

  Sergio R May 14, 2020 10:04

  - 0 +
• 
  Hi Sergio,
  
  Thanks for the suggestion.  Is there a straight-forward way of excluding only Windows users from the Change Automation project? 
  
  I would really like to keep the SQL logins in source control so I can keep tabs easily on permissions.  For example on the primary database I'm working on there are 10 Windows users and 40 sql logins to keep track of.
  
  Cheers,
  Nick

  Nick_Foster May 14, 2020 14:50

  - 0 +
• 
  So I've been playing with the filters in SQL Compare 14 in order to create something I can use in the Change Automation projects.
  
  When I exclude users from the comparison they are still included in the role definitions, for example
  

  <div>-- Role</div><div>CREATE ROLE [EbCollSqlgen]</div><div>AUTHORIZATION [dbo]</div><div>GO</div><div>ALTER ROLE [EbCollSqlgen] ADD MEMBER [ATTENDA-HQ\EbCollSqlgen]</div><div>GO
  </div>

  Do these not get validated then?  Will the deploy still fail if the role membership includes Windows users not in the current domain?

  Nick_Foster May 15, 2020 15:52

  - 0 +
• 
  Hi Nick,
  
  Regarding your first question, you could for example filter all out all the objects that contain "\" since only Windows users will have a slash in the name.
  
  Regarding the second question, Filters only exclude the user objects, they do not eliminate permissions or role memberships, to deal with that you can use Comparison Options.
  
  For example, to prevent SQL Change Automation from attempting to add user's role membership, you should set the "Ignore user's permissions and role memberships" SQL Compare option to true in your .sqlproj file, as explained in the following document:
  
  https://documentation.red-gate.com/sca/developing-databases/concepts/advanced-concepts/comparison-and-script-generation-options
  
  Kind Regards,

  Sergio R May 15, 2020 16:06

  - 0 +
• 
  I'm assuming "Ignore user's permissions and role memberships" will apply to all users, not just the Windows ones. Is that correct.

  Nick_Foster May 15, 2020 16:37

  - 0 +
• 
  Yes, that's correct, there isn't an option to just the ignore the permission to Windows users.
  This option supports what we consider to be best practice when allocation permissions in databases that are part of a DevOps process: you should allocate permissions to roles rather than users and then at the various environments you can have environment specific users allocated to those roles.

  Sergio R May 15, 2020 18:27

  - 0 +
• 
  Thanks for the help Sergio, most apprciated.  It's a shame I can't just ignore the Windows users, I would love to control the SQL logins with the Change Automation project.
  
  I don't suppose I could raise it as a feature request for future versions?  

  Nick_Foster May 18, 2020 12:50

  - 0 +
• 
  Hi Nick,
  
  If you wish you can suggest that on our Uservoice forum at https://redgate.uservoice.com/forums/267000-sql-change-automation
  
  Kind Regards,

  Sergio R May 18, 2020 15:09

  - 0 +

Add comment

Please sign in to leave a comment.