Changing the BCM database owner

Changing the BCM database owner

When I create my shared Outlook 2007 with Business Contact Manager database on my Small Business Server 2003, I did so while logged into my server as the domain administrator. That’s because the domain administrator is the only account that has permission to log on locally to the server, and a lot of the setup has to be done at the server console. But if you do that, then the domain administrator is going to be the owner of your database. And you probably don’t want that, because there are things that only the database owner can do in BCM 2007, like create user-defined fields. But the domain administrator account usually does not log into a workstation and open Outlook. So I had to change the database owner.

Before you go through the rest of these steps, you probably want to check to see who the database owner is. To do that, use these menu items in Outlook with BCM:

You’ll see the database owner displayed:

In the screen shot above, you’ll see that I’ve already successfully changed the owner to my regular user account. It used to have the domain administrator account there. If you’ve already got the name of the user who is supposed to be the owner, then you should be all set and you can skip the rest. If not, read on.

I am decidedly not a SQL maven, but I do know how to do a Google search for SQL 2005 change database owner. That search led me to the SQL Server 2005 Books Online at MSDN. I was able to muddle my way through a series of commands that enabled me to change the database owner from SCHRAG\administrator to SCHRAG\david. (Note to SBS gurus: Yes, I have actually renamed my administrator account, but if I told you what it is here, that would be defeating the purpose, wouldn’t it?)

I’m sure that any serious SQL admin would laugh at the ugliness of the instructions I’m about to give, but they worked for me. Log into the server as the domain admin, get to a command prompt, and do the following [do not include the stuff in brackets; that’s my own commentary]:

>sqlcmd -S .\mssmallbiz [where mssmallbiz is the name of your SQL instance; mssmallbiz is recommended]

1>use bcm_db [where bcm_db is the name of your BCM database; you named it during setup]

2>go

Changed database context to ‘BCM_DB’.

1>sp_dropuser @name_in_db = ‘DOMAIN\user’ [where DOMAIN is your Windows domain and user is the name of the user who will eventually be the owner; if you don’t drop this user first, you can’t make him or her the owner; I think the DROP USER command ought to work instead of sp_dropuser, but it didn’t work for me]

2>go

1>sp_changedbowner @loginame = ‘DOMAIN\user’ [note that there’s only one n in loginame]

2>go

1>exit

Now go back into Outlook and go to the Manage Database screen. Is the new owner’s name shown there? Great!

Posted in All, BCM, Office 2007, Software, Technology on Feb 8th, 2007, 2:51 am by David Schrag   

8 Responses

  1. Nick Lloyd
    March 31st, 2009 | 5:31 am

    Thanks Dave, just fixed the problem for me.

  2. April 30th, 2009 | 2:12 pm

    Works like a charm thanks

  3. August 6th, 2009 | 1:24 pm

    This did not work when I cut and paste ‘DOMAIN\user’

    The syntax it is looking for is this: ‘DOMAIN\user’

    Note the different single quotes used just FYI for any other users that try this.

    Thanks
    *MK*

  4. September 17th, 2009 | 2:22 pm

    Awesome, worked as advertised. And Mark’s comments above are valid, the single quotes have to be manually replaced or a syntax error will result.

  5. Mark
    October 8th, 2009 | 7:00 am

    Thanks this work like a charm.

    The absence of responses from the serious SQL admin’s must mean that your solution is the best.

  6. November 12th, 2011 | 11:24 am

    I tried but I get the error “the database principle owns a schema in the database, and cannot be dropped”. The schemas are all greyed out in SQL studio so I cannot unselect them.

  7. December 8th, 2014 | 1:03 am

    Worked perfectly. Thanks so much. This is the only reference i’ve found online for this code. <3

  8. Jon
    October 24th, 2016 | 7:53 pm

    I was able to do this with SQL Server Management Studio on the server, connect to:
    localhost\MSSMLBIZ

    Expand:
    Databases > DatabaseName > Security > Users
    Double-click User, and check off db_owner under Database role membership.

    It looks like it allows you to do this for multiple users – doesn’t actually change the database owner, just gives you the privileges.

Leave a reply