Change Table Ownership.

I have a DB that I moved to another server recently and in the process we
created a new user/pass combination.
All the tables for the main app are listed as "system" and the main app runs
great.
There is a secondary app using 12 tables in the DB and although we changed
the user/pass in the app and it connects fine the app won't run saying
"Invalid object name 'tablename'."
The table is there. The only thing I notice that seems odd is the tables are
listed as "user" and not "system" and the user assigned is the old user.
How can I either,
A. change what user owns the table? or,
B. Change it from a "user" table to a "system" table?
Thanks for any replies.
Check out sp_changeobjectowner in BOL.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>
|||System tables are there for SQL to run properly. User tables are what you
put information into. You don't need to or want to change a user table to a
system table.
You probably do need to change the owner of the tables. Look up
sp_changeobjectowner on BOL (Books On-Line) for exact instructions on how to
do this.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>
|||> "Invalid object name 'tablename'."
Have you tried explicitly naming the object? e.g. dbo.tablename?
Also see http://www.aspfaq.com/2284
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Thanks for the replies guys.
What I did was ran into the lazy GUI method.

>EM>Design Table>relationships icon from the top>Table tab> Change
ownership.
All set, app is behaving again.
Thanks.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>

Change Table Ownership.

I have a DB that I moved to another server recently and in the process we
created a new user/pass combination.
All the tables for the main app are listed as "system" and the main app runs
great.
There is a secondary app using 12 tables in the DB and although we changed
the user/pass in the app and it connects fine the app won't run saying
"Invalid object name 'tablename'."
The table is there. The only thing I notice that seems odd is the tables are
listed as "user" and not "system" and the user assigned is the old user.
How can I either,
A. change what user owns the table? or,
B. Change it from a "user" table to a "system" table?
Thanks for any replies.Check out sp_changeobjectowner in BOL.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>|||System tables are there for SQL to run properly. User tables are what you
put information into. You don't need to or want to change a user table to a
system table.
You probably do need to change the owner of the tables. Look up
sp_changeobjectowner on BOL (Books On-Line) for exact instructions on how to
do this.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>|||> "Invalid object name 'tablename'."
Have you tried explicitly naming the object? e.g. dbo.tablename?
Also see http://www.aspfaq.com/2284
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks for the replies guys.
What I did was ran into the lazy GUI method.
>EM>Design Table>relationships icon from the top>Table tab> Change
ownership.
All set, app is behaving again.
Thanks.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>

Change Table Ownership.

I have a DB that I moved to another server recently and in the process we
created a new user/pass combination.
All the tables for the main app are listed as "system" and the main app runs
great.
There is a secondary app using 12 tables in the DB and although we changed
the user/pass in the app and it connects fine the app won't run saying
"Invalid object name 'tablename'."
The table is there. The only thing I notice that seems odd is the tables are
listed as "user" and not "system" and the user assigned is the old user.
How can I either,
A. change what user owns the table? or,
B. Change it from a "user" table to a "system" table?
Thanks for any replies.Check out sp_changeobjectowner in BOL.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>|||System tables are there for SQL to run properly. User tables are what you
put information into. You don't need to or want to change a user table to a
system table.
You probably do need to change the owner of the tables. Look up
sp_changeobjectowner on BOL (Books On-Line) for exact instructions on how to
do this.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>|||> "Invalid object name 'tablename'."
Have you tried explicitly naming the object? e.g. dbo.tablename?
Also see http://www.aspfaq.com/2284
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks for the replies guys.
What I did was ran into the lazy GUI method.

>EM>Design Table>relationships icon from the top>Table tab> Change
ownership.
All set, app is behaving again.
Thanks.
"Jim" <Jim@.abc.com> wrote in message
news:OX%234ZddPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> I have a DB that I moved to another server recently and in the process we
> created a new user/pass combination.
> All the tables for the main app are listed as "system" and the main app
runs
> great.
> There is a secondary app using 12 tables in the DB and although we changed
> the user/pass in the app and it connects fine the app won't run saying
> "Invalid object name 'tablename'."
> The table is there. The only thing I notice that seems odd is the tables
are
> listed as "user" and not "system" and the user assigned is the old user.
> How can I either,
> A. change what user owns the table? or,
> B. Change it from a "user" table to a "system" table?
>
> Thanks for any replies.
>
>
>

change table ownership 2005

I have never been able to get this to work for 2005. Can anyone help
me troubleshoot my problem? I run this:
ALTER AUTHORIZATION ON db.table TO dbo;
GO
And get incorrect syntax near the keyword 'authorization'
I am supposed to run it as a query right?That's the correct syntax, yes. Does the following script run without error
for you:
use tempdb
go
create user auser without login
go
create table xyz (xxyyzz int)
go
alter authorization on xyz to auser
go
drop table xyz
go
drop user auser
go
--
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
<glayla@.gmail.com> wrote in message
news:1182458122.375047.227480@.k79g2000hse.googlegroups.com...
>I have never been able to get this to work for 2005. Can anyone help
> me troubleshoot my problem? I run this:
> ALTER AUTHORIZATION ON db.table TO dbo;
> GO
> And get incorrect syntax near the keyword 'authorization'
> I am supposed to run it as a query right?
>

change table ownership 2005

I have never been able to get this to work for 2005. Can anyone help
me troubleshoot my problem? I run this:
ALTER AUTHORIZATION ON db.table TO dbo;
GO
And get incorrect syntax near the keyword 'authorization'
I am supposed to run it as a query right?
That's the correct syntax, yes. Does the following script run without error
for you:
use tempdb
go
create user auser without login
go
create table xyz (xxyyzz int)
go
alter authorization on xyz to auser
go
drop table xyz
go
drop user auser
go
--
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
<glayla@.gmail.com> wrote in message
news:1182458122.375047.227480@.k79g2000hse.googlegr oups.com...
>I have never been able to get this to work for 2005. Can anyone help
> me troubleshoot my problem? I run this:
> ALTER AUTHORIZATION ON db.table TO dbo;
> GO
> And get incorrect syntax near the keyword 'authorization'
> I am supposed to run it as a query right?
>

change table ownership 2005

I have never been able to get this to work for 2005. Can anyone help
me troubleshoot my problem? I run this:
ALTER AUTHORIZATION ON db.table TO dbo;
GO
And get incorrect syntax near the keyword 'authorization'
I am supposed to run it as a query right?That's the correct syntax, yes. Does the following script run without error
for you:
--
use tempdb
go
create user auser without login
go
create table xyz (xxyyzz int)
go
alter authorization on xyz to auser
go
drop table xyz
go
drop user auser
go
--
--
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
<glayla@.gmail.com> wrote in message
news:1182458122.375047.227480@.k79g2000hse.googlegroups.com...
>I have never been able to get this to work for 2005. Can anyone help
> me troubleshoot my problem? I run this:
> ALTER AUTHORIZATION ON db.table TO dbo;
> GO
> And get incorrect syntax near the keyword 'authorization'
> I am supposed to run it as a query right?
>

Change ownership on a table

Hi,
Does anyone know the syntax to change the ownership on a table? I want to change it from what it is now to the dbo owner.
Thanks!sp_changeobjectowner 'owner.tablename','newowner'|||Hi,
Thanks for the reply, it was actually something that my web host had to do for me, I tried what you suggested but the server wouldnt give permission, thanks for the tip =)|||Originally posted by krs74
Hi,
Thanks for the reply, it was actually something that my web host had to do for me, I tried what you suggested but the server wouldnt give permission, thanks for the tip =)

You should be the owner or sa to do that job

Change Ownership of Tables

I would like to change the ownership of the few tables from a user to dbo.
What is the best way to do this ?
Never done this before. What possible impact could this have ?
RK
Hi RK
Please read about sp_changeobjectowner in the Books Online
Any code that references object owners will have to be changed
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:1EF21A6E-4B7A-4A6E-BE50-D4F75FC5FAFC@.microsoft.com...
>I would like to change the ownership of the few tables from a user to dbo.
> What is the best way to do this ?
> Never done this before. What possible impact could this have ?
> --
> RK
|||Even tho this is stated in BOL. sp_changeobjectowner This procedure removes
all existing permissions from the object. You will need to reapply any
permissions you want to keep after running sp_changeobjectowner.
For this reason, it is recommended that you script out existing permissions
before running sp_changeobjectowner. Once ownership of the object has been
changed, you may use the script to reapply permissions. You will need to
modify the object owner in the permissions script before running.
Sometimes overlooked
usually not twice.
"RK73" wrote:

> I would like to change the ownership of the few tables from a user to dbo.
> What is the best way to do this ?
> Never done this before. What possible impact could this have ?
> --
> RK

Change Ownership of Tables

I would like to change the ownership of the few tables from a user to dbo.
What is the best way to do this ?
Never done this before. What possible impact could this have ?
--
RKHi RK
Please read about sp_changeobjectowner in the Books Online
Any code that references object owners will have to be changed
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:1EF21A6E-4B7A-4A6E-BE50-D4F75FC5FAFC@.microsoft.com...
>I would like to change the ownership of the few tables from a user to dbo.
> What is the best way to do this ?
> Never done this before. What possible impact could this have ?
> --
> RK|||Even tho this is stated in BOL. sp_changeobjectowner This procedure removes
all existing permissions from the object. You will need to reapply any
permissions you want to keep after running sp_changeobjectowner.
For this reason, it is recommended that you script out existing permissions
before running sp_changeobjectowner. Once ownership of the object has been
changed, you may use the script to reapply permissions. You will need to
modify the object owner in the permissions script before running.
Sometimes overlooked
usually not twice.
"RK73" wrote:

> I would like to change the ownership of the few tables from a user to dbo.
> What is the best way to do this ?
> Never done this before. What possible impact could this have ?
> --
> RK

Change Ownership of Tables

I would like to change the ownership of the few tables from a user to dbo.
What is the best way to do this ?
Never done this before. What possible impact could this have ?
--
RKHi RK
Please read about sp_changeobjectowner in the Books Online
Any code that references object owners will have to be changed
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:1EF21A6E-4B7A-4A6E-BE50-D4F75FC5FAFC@.microsoft.com...
>I would like to change the ownership of the few tables from a user to dbo.
> What is the best way to do this ?
> Never done this before. What possible impact could this have ?
> --
> RK|||Even tho this is stated in BOL. sp_changeobjectowner This procedure removes
all existing permissions from the object. You will need to reapply any
permissions you want to keep after running sp_changeobjectowner.
For this reason, it is recommended that you script out existing permissions
before running sp_changeobjectowner. Once ownership of the object has been
changed, you may use the script to reapply permissions. You will need to
modify the object owner in the permissions script before running.
Sometimes overlooked
usually not twice.
"RK73" wrote:
> I would like to change the ownership of the few tables from a user to dbo.
> What is the best way to do this ?
> Never done this before. What possible impact could this have ?
> --
> RK

change ownership of objects

A co-worker recently quit the company, and all of his stored
procedures and DTS packages are apparently under his name. Is there
any way to change his account to another (ie administrator or SA)'
I am afraid to delete his account in fear all the stored procedures
will be lost. (we have backups, but ... ugh)
(running mssql server 2000)Hello,
Fiirst of all change the password for his account to a one with special
charecters, so as no one can access. After that you could use
sp_changeobjectowner system proc to change the owner of all object from that
user to DBO. take
alook into sp_changeobjectowner in books online..Once you completed the
object owner to DBO you could drop the user and
login associated.
Thanks
Hari
"berwiki" <steveberwick@.gmail.com> wrote in message
news:1171656219.472511.150980@.m58g2000cwm.googlegroups.com...
>A co-worker recently quit the company, and all of his stored
> procedures and DTS packages are apparently under his name. Is there
> any way to change his account to another (ie administrator or SA)'
> I am afraid to delete his account in fear all the stored procedures
> will be lost. (we have backups, but ... ugh)
>
> (running mssql server 2000)
>|||To add on to my previous post to change DTS owner use:-
use msdb
go
sp_reassign_dtspackageowner [@.name =] 'name',[@.id =] 'id',[@.newl
oginname =]
'newloginname'
Thanks
Hari
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:...
> Hello,
> Fiirst of all change the password for his account to a one with special
> charecters, so as no one can access. After that you could use
> sp_changeobjectowner system proc to change the owner of all object from
> that user to DBO. take
> alook into sp_changeobjectowner in books online..Once you completed the
> object owner to DBO you could drop the user and
> login associated.
> Thanks
> Hari
> "berwiki" <steveberwick@.gmail.com> wrote in message
> news:1171656219.472511.150980@.m58g2000cwm.googlegroups.com...
>|||To add on Hari's reply
Run the output in the QA
SELECT 'EXEC sp_changeobjectowner '''+ROUTINE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMsShipped')=0
AND ROUTINE_SCHEMA != 'dbo'
"berwiki" <steveberwick@.gmail.com> wrote in message
news:1171656219.472511.150980@.m58g2000cwm.googlegroups.com...
>A co-worker recently quit the company, and all of his stored
> procedures and DTS packages are apparently under his name. Is there
> any way to change his account to another (ie administrator or SA)'
> I am afraid to delete his account in fear all the stored procedures
> will be lost. (we have backups, but ... ugh)
>
> (running mssql server 2000)
>

Change ownership of model and msdb to 'sa'

We are trying to exec sp_changedbowner 'sa' on model database to change
ownership from the service account 'service_sql' to 'sa' account
However, we got the follow error:
15109 16 Cannot change the owner of the master database.
Is there any know how to Change ownership of model and msdb to 'sa' if in
case 'sa' is not owner of msdb and model?
Thanks!!From BOL:
The owner of the master, model, or tempdb system databases
cannot be changed.
- Rob
>--Original Message--
>We are trying to exec sp_changedbowner 'sa' on model
database to change
>ownership from the service account 'service_sql' to 'sa'
account
>However, we got the follow error:
>15109 16 Cannot change the owner of the master database.
>Is there any know how to Change ownership of model and
msdb to 'sa' if in
>case 'sa' is not owner of msdb and model?
>Thanks!!
>.
>|||I am facing the same problem as Maggie. My Production's SQL2k MSDB & Model
database owner is my old NT Domain Administrator (olddomain/administrator).
Not sure how it ended that way. Master owner is SA.
We have completed domain migration to Active Directory, if sp_changedbowner
is not going to allow me to change DB owner for MSDB & Model, does that mean
I still got to keep my old domain forever ?
There must be a work-around for this.
"Rob" wrote:
> From BOL:
> The owner of the master, model, or tempdb system databases
> cannot be changed.
> - Rob
> >--Original Message--
> >We are trying to exec sp_changedbowner 'sa' on model
> database to change
> >ownership from the service account 'service_sql' to 'sa'
> account
> >However, we got the follow error:
> >15109 16 Cannot change the owner of the master database.
> >Is there any know how to Change ownership of model and
> msdb to 'sa' if in
> >case 'sa' is not owner of msdb and model?
> >
> >Thanks!!
> >
> >.
> >
>

Change ownership of model and msdb to 'sa'

From BOL:
The owner of the master, model, or tempdb system databases
cannot be changed.
- Rob

>--Original Message--
>We are trying to exec sp_changedbowner 'sa' on model
database to change
>ownership from the service account 'service_sql' to 'sa'
account
>However, we got the follow error:
>15109 16 Cannot change the owner of the master database.
>Is there any know how to Change ownership of model and
msdb to 'sa' if in
>case 'sa' is not owner of msdb and model?
>Thanks!!
>.
>
I am facing the same problem as Maggie. My Production's SQL2k MSDB & Model
database owner is my old NT Domain Administrator (olddomain/administrator).
Not sure how it ended that way. Master owner is SA.
We have completed domain migration to Active Directory, if sp_changedbowner
is not going to allow me to change DB owner for MSDB & Model, does that mean
I still got to keep my old domain forever ?
There must be a work-around for this.
"Rob" wrote:

> From BOL:
> The owner of the master, model, or tempdb system databases
> cannot be changed.
> - Rob
> database to change
> account
> msdb to 'sa' if in
>

Change ownership of model and msdb to 'sa'

From BOL:
The owner of the master, model, or tempdb system databases
cannot be changed.
- Rob

>--Original Message--
>We are trying to exec sp_changedbowner 'sa' on model
database to change
>ownership from the service account 'service_sql' to 'sa'
account
>However, we got the follow error:
>15109 16 Cannot change the owner of the master database.
>Is there any know how to Change ownership of model and
msdb to 'sa' if in
>case 'sa' is not owner of msdb and model?
>Thanks!!
>.
>I am facing the same problem as Maggie. My Production's SQL2k MSDB & Model
database owner is my old NT Domain Administrator (olddomain/administrator).
Not sure how it ended that way. Master owner is SA.
We have completed domain migration to Active Directory, if sp_changedbowner
is not going to allow me to change DB owner for MSDB & Model, does that mean
I still got to keep my old domain forever ?
There must be a work-around for this.
"Rob" wrote:

> From BOL:
> The owner of the master, model, or tempdb system databases
> cannot be changed.
> - Rob
>
> database to change
> account
> msdb to 'sa' if in
>

Change ownership of DTS package

Howdo I change the ownership of DTS packages?
Thanks
In the msdb database there is stored procedure named
sp_reassign_dtspackageowner that will help you change it.
Here is a sample syntax:
msdb..sp_reassign_dtspackageowner
@.name = 'Package Name',
@.id = '-7DED-11D4-BF04-00B0D057DBE4',
@.newloginname = 'new user'
You can get the package ID (@.id) from the msdb..sysdtspackages table (ID
column).
"Mecn" wrote:

> Howdo I change the ownership of DTS packages?
> Thanks
>
>

Change ownership of DTS package

Howdo I change the ownership of DTS packages?
ThanksIn the msdb database there is stored procedure named
sp_reassign_dtspackageowner that will help you change it.
Here is a sample syntax:
msdb..sp_reassign_dtspackageowner
@.name = 'Package Name',
@.id = '-7DED-11D4-BF04-00B0D057DBE4',
@.newloginname = 'new user'
You can get the package ID (@.id) from the msdb..sysdtspackages table (ID
column).
"Mecn" wrote:
> Howdo I change the ownership of DTS packages?
> Thanks
>
>

Change ownership of all objects owned by

HI, I thought I read somewhere about a Stored Procedure that can be used to
change all objects owned by x to owned by y. Does anyone know of such SP,
tell me about it, please. OR Tell me a better way of changing ownership of
ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
another SQL login I've created with SA privileges, Then I can change the PW
of sa. OR can this not be done. ? PLEASE ADVISE!!This free tool may be a start...
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1116990,00.html?track=NL-464&ad=524311USCA
It uses a parameter of 'IF ^$^ <> ^dbo^ EXECUTE sp_changeobjectowner
^$.*^,^dbo^' to change the owner of selected objects.
"WANNABE" wrote:
> HI, I thought I read somewhere about a Stored Procedure that can be used to
> change all objects owned by x to owned by y. Does anyone know of such SP,
> tell me about it, please. OR Tell me a better way of changing ownership of
> ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
> another SQL login I've created with SA privileges, Then I can change the PW
> of sa. OR can this not be done. ? PLEASE ADVISE!!
>
>

Change ownership of all objects owned by

HI, I thought I read somewhere about a Stored Procedure that can be used to
change all objects owned by x to owned by y. Does anyone know of such SP,
tell me about it, please. OR Tell me a better way of changing ownership of
ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
another SQL login I've created with SA privileges, Then I can change the PW
of sa. OR can this not be done. ? PLEASE ADVISE!!This free tool may be a start...
http://searchsqlserver.techtarget.c...4&ad=524311USCA
It uses a parameter of 'IF ^$^ <> ^dbo^ EXECUTE sp_changeobjectowner
^$.*^,^dbo^' to change the owner of selected objects.
"WANNABE" wrote:

> HI, I thought I read somewhere about a Stored Procedure that can be used t
o
> change all objects owned by x to owned by y. Does anyone know of such SP,
> tell me about it, please. OR Tell me a better way of changing ownership
of
> ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
> another SQL login I've created with SA privileges, Then I can change the P
W
> of sa. OR can this not be done. ? PLEASE ADVISE!!
>
>

Change ownership for User Defined Data Types

Hello,

Does anybody know an easy way to change User Defined Data Types ownership to dbo, without dropping dependent objects?

I know that there is a st. proc sp_changeobject owner, but it does not deal with this particular db objects. Is there a similar st.procedure or script that would do the same operation?

Thank youThere are no dependent object to user-defined datatypes. If you try scripting out a table that was created with user-defined datatypes, you'll find it scripts the original datatype.
Neither do objects "inherit" changes made to User-defined datatype.
Thus: User-defined datatypes are candidates for the most useless and misleading features of SQL Server, perhaps exceeded only by the Maintenance Plan Wizard.|||Well, may be I expressed myself wrong. I was trying to change ownership for UDDTs, and I the only way I knew was to drop them and recreate as "dbo". But these UDDTs were already used by many tables and stored procedures. So I would need to drop those as well and then restore the data. Too much hassle.
Anyway, I have already found the solution. This is the script:

sp_configure 'Allow Update',1
Reconfigure WITH OVERRIDE

Update sysTypes set uid = user_id('dbo') Where name = …

sp_configure 'Allow Update',0
Reconfigure WITH OVERRIDE

Change ownership

Hello I'm trying to change the ownership using this command :

sp_changeobjectowner'govern.NA_WEB_SUBS_HITS','dbo'

and then I received this following error message :

Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 75
Object 'govern.NA_WEB_SUBS_HITS' does not exist or is not a valid object for this operation.

Can anyone help please?

Thanks,

Stanley

You need to speicify the current owner on this object to change:

sp_changeobjectowner 'Dbobject', 'dbo'

|||

NOTE: sp_changeobjectowner has been deprecated in SQL Server 2005. I strongly recommend using the new DDL designed for changing ownership: ALTER AUTHORIZATION.

For more information please refer to BOL:

· ALTER AUTHORIZATION (Transact-SQL) http://msdn2.microsoft.com/en-us/library/ms187359.aspx

· sp_changeobjectowner (Transact-SQL) http://msdn2.microsoft.com/en-us/library/ms177519.aspx

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

What if the "current owner" does not exist? After a total system crash we made a db restore. All the objects belong to a sql user that does not exist anymore (different sid). What can we do now? http://support.microsoft.com/kb/275312/en-us has a good script generator but we got stuck "oldOwner.dbObject" does not exist!!!