Assign dbo role to login without creating a new user in db

Question

I would like to create assign dbo rights on an existing database to an existing login without creating a new user in the database. I would like to have the login use dbo user, like a member of sa server role does.

All I have found involves creating a new db user.

Thanks!

Answer 1

If at all possible, this should be fixed from the deployment side of things and not from a questionable modification of the target database side of things (referring to the conversation in the comments on the Question related to using the deprecated sp_addalias).

Depending on what the exact problem is, there are various configuration options of DacPac deployment that can be used to get around such conflicts. These options can either be specified on the command line if running SqlPackage, or can be placed into a DAC Publish Profile (i.e. an XML config file).

The options of interest here are listed in the Publish Parameters, Properties, and SQLCMD Variables section of the SqlPackage page.

To start with, do you have any of the following set to True:

• DropObjectsNotInSource (default: False)
• DropRoleMembersNotInSource (default: False)
• IgnoreLoginSids (default: True)

If you have the DropObjectsNotInSource property set to True, then try adding:

• DoNotDropObjectType=Users
• ExcludeObjectType=Users
• IgnoreRoleMembership=True
• IgnoreUserSettingsObjects=True ??

If needing to specify the ExcludeObjectType property, that would look as follows, depending on where it is specified:

• On the command-line: /p:ExcludeObjectType=Users

• In a DAC Publish Profile:

  <?xml version="1.0" encoding="utf-8"?>
  <Project ToolsVersion="12.0"
           xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
      <PropertyGroup>
          <ExcludeObjectType>Users</ExcludeObjectType>
      </PropertyGroup>
  </Project>