none
The database owner SID recorded in the master database differs from the database owner SID recorded in database 'DB'?

    Question

  • Hello, ran a batch that's purpose is to basically setup a clr stored procedure... on the line that creates the assembly I get this error:

    Msg 33009, Level 16, State 2, Line 2
    The database owner SID recorded in the master database differs from the database owner SID recorded in database 'DB'. You should correct this situation by resetting the owner of database 'DB' using the ALTER AUTHORIZATION statement.

    this batch runs fine on my development server, now I am trying to move this into production and have run into this problem. I'm assuming it has to do with the fact that the production server was setup from doing a backup of the database on the development server and restoring that to the production server. Both servers are setup with mixed mode auth.

    the error says to use ALTER AUTHORIZATION to fix this, but I want to be sure I do the right thing... use it? how exactly?

    here is the batch that is causing the message for reference:

    ALTER DATABASE db SET trustworthy ON
    go
    
    use db
    go
    
    sp_configure 'clr enabled', 1
    go
    
    reconfigure
    go
    
    create assembly DP_SQLExtensions from 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPDB\MSSQL\Binn\DC_SQLExtensions.dll'
    with permission_set = unsafe
    go
    
    create procedure [dbo].[sp_SendMail]
    	@From [nvarchar] (100),
    	@Recipients [nvarchar] (1000),
    	@Subject [nvarchar] (150),
    	@MessageBody [nvarchar] (4000),
    	@HighPriority [bit],
    	@SMTPServer [nvarchar] (75),
    	@Port [int]
    with execute as caller
    as external name [DC_SQLExtensions].[DC_SQLExtensions.SQL_SMTP_Client].[SendMail]
    
    Wednesday, November 11, 2009 2:39 PM

Answers

  • wound up doing this with success:

    exec sp_changedbowner [DOMAIN\User]

    that made my domain\user account the owner and mapped it to dbo, then my original batch that caused the error was able to complete succesfully.

    Thank you for all your help Adam, I really appreciate it. I would not have gone down this path if not for your suggestions and advice.
    • Marked as answer by c0pe Thursday, November 12, 2009 7:44 PM
    Wednesday, November 11, 2009 8:15 PM

All replies

  • Do you know who originally created the database?

    Abdallah, PMP, ITIL, MCTS
    Wednesday, November 11, 2009 2:56 PM
  • domain admin account from the development environment created db in the development environment, and performed the backup... then the domain admin account (different forest/domain) in the production environment restored the database to the production server
    Wednesday, November 11, 2009 3:45 PM
  • When a backup restore operation occurs on a differing sql server instance the SIDs may not be aligned correctly.  This is often reffered to as an orphaned user.  In this scenario, you have to issue an alter user command to sync the SIDs.


    ALTER USER [Domain\User] WITH LOGIN [Domain\User];

    http://jahaines.blogspot.com/
    Wednesday, November 11, 2009 3:55 PM
  • Hi thanks Adam... I did do that for my 'database' application user, which is the user that my application uses to do all database access stuff, but it was not done for the actual windows account (domain\administrator) that is used to administer the sql server... so that makes sense... do I do this against my database? or the master database? I'm assuming my database, but just want to be sure

    thanks again
    Wednesday, November 11, 2009 4:03 PM
  • just realized my dev server has a user mapping shown (properties of domain\user under 'security -> logins') for the domain admin account to 'dbo' in my 'db' database and the production server does not. Although under the db database -> security -> logins, there is not domain admin account listed.  I'm thinking just adding the user mapping from domain admin account login to the database dbo will take care of this? is that essentially the same thing the 'alter user' command would be doing? but again, there is no domain\user user listed under the database
    Wednesday, November 11, 2009 4:27 PM
  • That means you need to either drop/create the database user, or issue the alter user command in the database.  I dont think the GUI will let you create a mapping because under the hood it will try to create the database user, which will fail because the user already exists.
    http://jahaines.blogspot.com/
    Wednesday, November 11, 2009 4:30 PM
  • actually, the database user 'domain\user' does not exist, at least I assume it does not because it is not listed under database -> security -> users

    thats also why I was wondering if the 'alter user' command would work, because the user doesn't exist... I have the properties open for the server login domain\user, on the database mapping page, I have it filled out to map domain\user to 'dbo' in my database... have not clicked 'ok' yet because I don't want to make things worse.. what do you think?
    Wednesday, November 11, 2009 4:37 PM
  • If the user is the owner he may be listed as dbo instead of his name; otherwise, he may not exist.  Check the properties of dbo to see what account it is working from.

    <<thats also why I was wondering if the 'alter user' command would work, because the user doesn't exist

    The alter login will definitely fail if the user does not exist.


    << I have it filled out to map domain\user to 'dbo' in my database... have not clicked 'ok' yet because I don't want to make things worse.. what do you think?

    You cannot really make it worse, the command will just fail. I say try creating the mapping.
    http://jahaines.blogspot.com/
    Wednesday, November 11, 2009 5:04 PM
  • ok, your right, GUI gave error that user already existed. I looked back at the dev machine and the database user dbo is mapped to domain\user but on the production machine the database user dbo is not mapped to anything, it's blank. So I see I need dbo 'user' mapped to my domain\user 'login'.

    Is the following the correct command for my case?

    use db;
    ALTER USER dbo WITH LOGIN domain\user;
    Wednesday, November 11, 2009 6:50 PM
  • Yes, that should do the trick.



    Edit: Make sure to use brackets around the domain user account; otherwise, the parser may scream at you.

    http://jahaines.blogspot.com/
    Wednesday, November 11, 2009 6:58 PM
  • ____, got error "cannot alter the user 'dbo'"

    now what?
    Wednesday, November 11, 2009 7:17 PM
  • hehe, I like the filtering of ' c r a p ' from my previous reply, nice touch.
    Wednesday, November 11, 2009 7:19 PM
  • Make someone else the dbo and then alter the user name.
    http://jahaines.blogspot.com/
    Wednesday, November 11, 2009 8:06 PM
  • wound up doing this with success:

    exec sp_changedbowner [DOMAIN\User]

    that made my domain\user account the owner and mapped it to dbo, then my original batch that caused the error was able to complete succesfully.

    Thank you for all your help Adam, I really appreciate it. I would not have gone down this path if not for your suggestions and advice.
    • Marked as answer by c0pe Thursday, November 12, 2009 7:44 PM
    Wednesday, November 11, 2009 8:15 PM
  • i came up with this... to make it a bit more dynamic.

    declare @user varchar(50)
    SELECT  @user = quotename(SL.Name)
      FROM  master..sysdatabases SD inner join master..syslogins SL
        on  SD.SID = SL.SID
     Where  SD.Name = DB_NAME()
    exec('exec sp_changedbowner ' + @user)


    James Peckham CSM, CSP, CAPM Developer and Agilist http://www.jamespeckham.com

    Wednesday, September 12, 2012 1:40 PM
  • Getting same error.

    Thanks

    Tuesday, October 16, 2012 6:02 AM
  • In my case the sid of dbo in both places master and user database has same now and inside databas user section dbo is used and user login name is not displaying, but the user login is sysadmin and also the db_owner of the database when I remove the sysadmin role only then it give error:client is not able to impersonate the login.

    Thanks

    Tuesday, October 16, 2012 6:29 AM
  • Thanks! this was very helpful
    Monday, June 24, 2013 12:13 AM