Jump to content

Recommended Posts

  • Administrators

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"

Link to post
Share on other sites

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.

Link to post
Share on other sites

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?

Link to post
Share on other sites

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.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...