mssql Change Object Owner

[Nathan]

This is a quick and easy way to change the owner of an object if needed with sp_changedobjectowner

 

sp_changeobjectowner 'oldownername.nameofobject', 'newowner'

example:
sp_changeobjectowner 'nathan.p_Customer1', 'nriley'

 

The following changes the owner from "nathan" to "nriley"

[wonderzz]

Great tip. This was one of the first things I learned when I started.

[haye55987]

Wow...I am jealous. In oracle, this requires a CTAS (Create Table as Select) to basically create a clone of the current table in the new schema (then copying indexes) and dropping the table in the old schema (or owner). Grants must also be in place and it can take a while to copy all of the data over depending on block size, segments, and extents.

[bryce12]

Yeah it's not a straight forward operation in Oracle. In MySQL things are lot simpler and I am actually glad that I am coding more in MySQL and less in Oracle these days. Does anyone know whether Oracle offers an easier way to change owner in the latest version?

[haye55987]

Yeah it's not a straight forward operation in Oracle. In MySQL things are lot simpler and I am actually glad that I am coding more in MySQL and less in Oracle these days. Does anyone know whether Oracle offers an easier way to change owner in the latest version?

 

They do not. I am not sure the back end behind MsSQL but in Oracle the owner actually determines the location the table sits in the data files. Changing owners can require actually moving the data files and tablespaces and even more all depending on how the Oracle database is set-up. I work in 11gr2 as the latest version and there is no easy way. I cannot think of a real reason to change the owner of a table that wouldn't show a flaw in the original spec.

 

What are some examples you find in MySQL that are valid points to change table permissions? In Oracle, to simplify, I would probably just write a wrapper procedure where I pass in a table name and a owner and have my procedure move the table.