DacPac exclude users and logins on export or import

 

Im doing some automation and in the middle of rewriting some 10-15 old, quirky scripts to one single and simple unit, which can:

  • Export a database scheme from MsSql2012 in production environment
  • Import the database scheme to MsSql2012 in development environment

    • Im doing this using the Microsoft.SqlServer.Dac in which i can export/import dac packs.

    It works nicely :), but after doing all my TDD and testing, it was time for the real thing and it turns out I had completely missed that we have 2 domains at my work.

    This means I get an error when I'm importing the dacpac in the dev environment, due to the fact that our 2 domains do not have full 2 way trust and of course the database has users from both domains. In particular the production domain users. I can see 3 viable solutions:

  • Gain full trust between environments. But this is not going to happen due the high level of security enforced at my work.
  • Import the dacpac without users.
  • Export to dacpac without users.

    • Ive read the documentation (which is vague of course), trying to find a set of options which could achieve 2) or 3). Ive testet a lot of combos but none that will work. The dac pac import keeps trying to create the users and fails since the production users cannot be verified in the dev environment.

    Im using Deploy and Extract to export/import. The different options can be found here:

  • Extract: http://technet.microsoft.com/en-us/library/microsoft.sqlserver.dac.dacextractoptions.aspx
  • Deploy: http://technet.microsoft.com/en-us/library/microsoft.sqlserver.dac.dacdeployoptions.aspx

    • No combination I have found works and Ive even read posts that suggest that its a bug in the DacPac. But I'm not sure. A last option I'm looking into right now is to use the GenerateScript method which hopefully can force to exclusion of users/logins, but I fear that its not going to work either. See (http://technet.microsoft.com/en-us/library/hh753526.aspx).

    Question: How can I exclude users/logins from my dacpac, either in the export or the import?

    UPDATE

    Current Extract Settings: 

    IgnoreUserLoginMappings = true,
VerifyExtraction = false,

    Current Deploy Settings: 

    CreateNewDatabase = true,
DeployDatabaseInSingleUserMode = true,
DisableAndReenableDdlTriggers = true,
IgnoreUserSettingsObjects = true,

    Update

    So far everything points to one ugly solution. 

    I can generate a deploy script using DacServices. 
Then i can parse the script (Remove logins etc).
Then write the result to a file.
And then call SQLCMD in an external process given it the generated and parsed file. 
Finally i must evaluate the result from the SQLCMD process.

    The reason for the use of SQLCMD is that the generated script apperently not is pure tsql, but uses stuff as ':setvar', which only SQLCMD can handle, as far as i can tell. Come on MS...

    Again if anyone know a better way to do this in C#, or know of a bug fix/update for the DacPac assemblies, please share. :)

    Update

    I found that stuff like IgnorePartitionSchemes also do not work. It seems that the namespace Microsoft.SqlServer.Dac, is not just a little but VERY buggy or lacking implementation.

    I managed to solve this problem with the following deployment of a dacpac file. The important setting was in ExcludeObjectTypes . 

    const string connectionString = @"Data Source=(LocalDB)MSSQLLocalDB...";
var dacServices = new DacServices(connectionString);

// show deployment in the output window.
dacServices.Message += (o, args) => Debug.WriteLine(args.Message);
dacServices.ProgressChanged += (o, args) => Debug.WriteLine(args.Status);

// load the file.
var dacpac = DacPackage.Load("file.dacpac");
var options = new DacDeployOptions
    {
         IgnorePermissions = true,
         IgnoreUserSettingsObjects = true,
         IgnoreLoginSids = true,
         IgnoreRoleMembership = true,

         // THIS IS THE MAGIC SETTING THAT FINALLY WORKED.
         ExcludeObjectTypes = new[] { 
             ObjectType.Users,
             ObjectType.Logins,
             ObjectType.RoleMembership }
    };
dacServices.Deploy(
    dacpac,
    "MyDbName",
    true,
    options);

    During deployment the important setting is the "IgnoreUserSettingsObjects" one. This will ignore users, user-> login and user->schema relationships. Setting that to true should be the main thing to get this working.

    On Extract you can also set the DacExtractOptions.IgnoreUserLoginMappings to true. In this case the user->login mapping is not included, but it looks like it's more important to just ignore users at deployment time. This does mean that you need to run a script after the deployment to ensure the correct users are present.

    I'm struggling with a similar issue. DACPACs are just a zip archive with 3 XML files in them. I found source code for a dacpac manipulation tool and modified that to strip the users and logins out of a DACPAC.

    链接地址: http://www.djcxy.com/p/77578.html

    上一篇: 为在JavaScript中传递给函数的对象分配新值

    下一篇: DacPac排除用户并登录导出或导入