how do I change schema owner in ms sql server?

Question

I currently have a website hosted developed with kentico 7. I tried importing the exported website into my localhost and that failed. In my findings, i realised that each table in the online version has a bvs (the database user created) prefix. I have tried creating the same scenario on my localhost without any luck. Does anyone have an idea ?

Here is the error displayed at importation.

Hourihan

what the error you are getting? create bvs user and schema on the localhost and then import. – Lubric

i did that already. I noticed I have the prefix dbo on all my tables in localhost and bvs on all tables in the online version. I have tried EXEC sp_changedbowner 'bvs' but it returned the error : The proposed new database owner is already a user or aliased in the database. – Hourihan

"I tried importing the exported website into my localhost and that failed" what failure did you get? – Moray

See my update for the error page. – Hourihan

Answer 1

You can change the schema owner using this command:

ALTER AUTHORIZATION ON SCHEMA::bvs TO db_owner;

Myrlmyrle

Answer 2

Try this out:

USE MyDB
GO
ALTER AUTHORIZATION ON SCHEMA::bvs TO dbo;
GO 
SP_DROPUSER 'bvs'
GO
SP_CHANGEDBOWNER 'bvs'

Colettecoleus

The database principal owns a schema in the database, and cannot be dropped. and The proposed new database owner is already a user or aliased in the database. respectively – Hourihan

I have. The result is what I posted in my comment. – Hourihan

@Rookie13 Could you also explain your answer? Such answers are always stronger. – Intimate

Stronger? I am suggesting Peter to change the authorization for user 'bvs' to dbo. so he can have permissions to drop and change dbowner. – Colettecoleus

@Rookie13, thanks for your responses so far. currently I have my tables with the prefix dbo while I want it as bvs (I'm trying to be as simple as possible). I believe if I can achieve that, my issues will be resolved. Any idea? – Hourihan

Check out this article and see if it provides any information that might be useful: mssqltips.com/sqlservertip/2620/… – Colettecoleus

WARNING: This will drop all existing permissions on objects in the schema. See dba.stackexchange.com/a/247459/176679. – Heathenish