Changing database and database object owners in SQL Server

I’ve managed to make a bit of a mess of the permissions in an SQL Server database for a personal project that I originally created through my web host’s web interface (before I could locate a copy of Enterprise Manager).

One of the things I’m trying to fix at the moment is the user who’s the owner of the database. It was originally set to sa because that’s who created the database through the web interface but all of the database objects were owned by my “programmer” user who I logged in as.

I think it’d be simpler (and I don’t think it’d be any less secure) if my programmer user was dbo and so all of the tables and stored procedures could be owned by dbo instead of explicitly owned by my programmer login. Then I wouldn’t have to hardcode the programmer username into all of the procedures and it’ll make the whole thing a bit easier to move between servers.

Changing which user is dbo is really straight forward. You just need to run the sp_changedbowner built in stored procedure passing the new username as a parameter:

To change the owner of the database you need to run the sp_changeobjectowner built in stored procedure passing the object name and new owner’s username as parameters: