Changing the owner of a table

Hi,
I have a table in SQL Server 2000, say its called: "tblName". When
I right mouse click on it and select "Properties" from the menu I see
that beside the "Owner" label is companyName\Administrator.
I want to change this to sa as I'm having problems creating an
index (I think its got to do with this). So my question is: how do you
change the owner of a table please?
Any comments/suggestions/advice greatly appreciated.
Thank you,
Al.
<almurph@.altavista.com> wrote in message
news:1e70ec60-b12a-49dc-b8d4-f070f15d739b@.j20g2000hsi.googlegroups.com...
> Hi,
> I have a table in SQL Server 2000, say its called: "tblName". When
> I right mouse click on it and select "Properties" from the menu I see
> that beside the "Owner" label is companyName\Administrator.
> I want to change this to sa as I'm having problems creating an
> index (I think its got to do with this). So my question is: how do you
> change the owner of a table please?
> Any comments/suggestions/advice greatly appreciated.
> Thank you,
> Al.
Take a look at the sp_changeobjectowner stored procedure. There are some
caveats to this process however. You may impact other views and stored
procedures that depend on that table having the
companyName\Administrator.Table1 name.
You cannot change the ownership to sa. You can change it to dbo.
As for your index, if you are the sa in the database, you could create your
index by specifying the qualified name of the table.
Example:
CREATE NONCLUSTERED INDEX IX_SomeIndex ON companyName\Administrator.Table1
(column list)
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Changing the owner of a table

Hi,
I have a table in SQL Server 2000, say its called: "tblName". When
I right mouse click on it and select "Properties" from the menu I see
that beside the "Owner" label is companyName\Administrator.
I want to change this to sa as I'm having problems creating an
index (I think its got to do with this). So my question is: how do you
change the owner of a table please?
Any comments/suggestions/advice greatly appreciated.
Thank you,
Al.<almurph@.altavista.com> wrote in message
news:1e70ec60-b12a-49dc-b8d4-f070f15d739b@.j20g2000hsi.googlegroups.com...
> Hi,
> I have a table in SQL Server 2000, say its called: "tblName". When
> I right mouse click on it and select "Properties" from the menu I see
> that beside the "Owner" label is companyName\Administrator.
> I want to change this to sa as I'm having problems creating an
> index (I think its got to do with this). So my question is: how do you
> change the owner of a table please?
> Any comments/suggestions/advice greatly appreciated.
> Thank you,
> Al.
Take a look at the sp_changeobjectowner stored procedure. There are some
caveats to this process however. You may impact other views and stored
procedures that depend on that table having the
companyName\Administrator.Table1 name.
You cannot change the ownership to sa. You can change it to dbo.
As for your index, if you are the sa in the database, you could create your
index by specifying the qualified name of the table.
Example:
CREATE NONCLUSTERED INDEX IX_SomeIndex ON companyName\Administrator.Table1
(column list)
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Changing DB Owner

Inadvertantly I moved several DB's from one SQL box to another however when
I reattached the DB's onte Target server I was logged in as the incorrect
user so the DB Owner shows up as my personal login and not the
Domain\SQLAdmin user which we are supposed to perform maintanence under
(oops). I suspect things worked because my login ID is domain admin. I did
find the command sp_changedbowner however I think it only works with SQL
users and not Windows Domain users. If this is correct could some one
please send/post the exact syntax to change the DB owner from Domain\mylogin
to Domain\SQLAdmin? Thanks!sp_changedbowner ought to work with both Windows and SQL logins. The
specified login must not already be a user in the database. Examples below.
--standard login
USE MyDatabase
EXEC sp_changedbowner 'SQLAdmin'
--Windows login
USE MyDatabase
EXEC sp_changedbowner 'Domain\SQLAdmin'
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike B." <Autobahn97@.hotmail.com> wrote in message
news:e$ytVNFhEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Inadvertantly I moved several DB's from one SQL box to another however
when
> I reattached the DB's onte Target server I was logged in as the incorrect
> user so the DB Owner shows up as my personal login and not the
> Domain\SQLAdmin user which we are supposed to perform maintanence under
> (oops). I suspect things worked because my login ID is domain admin. I
did
> find the command sp_changedbowner however I think it only works with SQL
> users and not Windows Domain users. If this is correct could some one
> please send/post the exact syntax to change the DB owner from
Domain\mylogin
> to Domain\SQLAdmin? Thanks!
>

Changing DB Owner

Inadvertantly I moved several DB's from one SQL box to another however when
I reattached the DB's onte Target server I was logged in as the incorrect
user so the DB Owner shows up as my personal login and not the
Domain\SQLAdmin user which we are supposed to perform maintanence under
(oops). I suspect things worked because my login ID is domain admin. I did
find the command sp_changedbowner however I think it only works with SQL
users and not Windows Domain users. If this is correct could some one
please send/post the exact syntax to change the DB owner from Domain\mylogin
to Domain\SQLAdmin? Thanks!
sp_changedbowner ought to work with both Windows and SQL logins. The
specified login must not already be a user in the database. Examples below.
--standard login
USE MyDatabase
EXEC sp_changedbowner 'SQLAdmin'
--Windows login
USE MyDatabase
EXEC sp_changedbowner 'Domain\SQLAdmin'
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike B." <Autobahn97@.hotmail.com> wrote in message
news:e$ytVNFhEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Inadvertantly I moved several DB's from one SQL box to another however
when
> I reattached the DB's onte Target server I was logged in as the incorrect
> user so the DB Owner shows up as my personal login and not the
> Domain\SQLAdmin user which we are supposed to perform maintanence under
> (oops). I suspect things worked because my login ID is domain admin. I
did
> find the command sp_changedbowner however I think it only works with SQL
> users and not Windows Domain users. If this is correct could some one
> please send/post the exact syntax to change the DB owner from
Domain\mylogin
> to Domain\SQLAdmin? Thanks!
>