1

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!

8
  • EXEC sp_addrolemember 'db_owner', 'your user'. Note that sp_addrolemember is announced as to be deprecated as of sql server 2014. – Kin Shah Nov 9 '15 at 18:20
  • That adds a db user to a role. I want to alias an existing user from a login – Metaphor Nov 9 '15 at 18:27
  • 1
    Can you explain why you want to avoid CREATE USER foo FROM LOGIN foo;? – Aaron Bertrand Nov 9 '15 at 18:30
  • 1
    No, "old" means it will stop working. See this note in the SQL Server 2005 documentation: This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. – Aaron Bertrand Nov 9 '15 at 19:39
  • 1
    Also, what does "doesn't like" mean? If you need database users to support your application, why can't they exist in your code / dacpacs? – Aaron Bertrand Nov 9 '15 at 19:40
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>
    
6
  • 1
    This information is very helpful, thank you. – Metaphor Nov 10 '15 at 13:20
  • @Metaphor No problem. Does that mean that one or more of these options solved the issue? If so, which one(s)? It would help to have it documented. – Solomon Rutzky Nov 10 '15 at 16:29
  • @Metaphor Also, next time you ask a question, please consider giving more backgroup on it. If it weren't for the discussion in the comments mentioning DacPacs, I would not have posted this answer. It would have helped if the question started with, "I am trying to deploy DacPacs but am running into a problem with Users that exist in the target database but not in the DacPac". Just FYI :-). – Solomon Rutzky Nov 10 '15 at 16:32
  • will do. I wasn't looking for a deployment solution though, we have different teams doing dev, db and deployment. I was originally just looking for an equivalent of sp_addalias. DoNotDropObjectType=Users will work with the help of the deployment team. I'll have to do batch files for the devs to deploy local dbs. – Metaphor Nov 10 '15 at 20:31
  • @Metaphor I understand the separation of teams part, but this really is a deployment issue. And the "alias" concept seems to be going away. But just to mention this, the dbo User that is in each database can be reassigned to a different login: it doesn't need to be associated with sa. But it can only be assigned to one Login at a time since it is an actual User, not a role. I don't know if that would help any, and I am not even sure that I would recommend it, but there ya go :-). Do you have Users that are in the DacPac? If not, you should try the IgnoreType=Users instead maybe? – Solomon Rutzky Nov 10 '15 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.