Changing the owner of a user-defined data type

Hi Guys

Wonder if you could help me.

Basically I produce an accounts package that uses a SQL 2000 DB as the
RDBMS. I always instruct users to login as 'sa' and the relevant
password when doing an update to my program, as sometimes I need to do
database changes for new stuff.

Found that one of my users has not only logged in with their login
name (in this case Edward), but have also made this login a 'db owner'
so that when I created 2 new user-defined data types they belong to
Edward rather than dbo.

This must have happened a long time ago, but now that they want to
move Edward round the roles and/or delete him from a copy of the
database that they have, they can't because he's the owner of these
user-defined types.

This brings me to the reason for my post, how can I change the owner
from Edward to dbo for these data types? I found an article on
technet of how to do this, but when it suggests changing my
user-defined type to standard format it doesn't seem to work.

Any ideas?

Rgds

RobbieHi

I don't think there is a way to do this. If there are dependencies on the
UDT they will all need to be removed, then you can drop and re-create it.

John

"Robbie" <google@.astraaccounts.co.uk> wrote in message
news:f588d13a.0310062351.46e1f3f3@.posting.google.c om...
> Hi Guys
> Wonder if you could help me.
> Basically I produce an accounts package that uses a SQL 2000 DB as the
> RDBMS. I always instruct users to login as 'sa' and the relevant
> password when doing an update to my program, as sometimes I need to do
> database changes for new stuff.
> Found that one of my users has not only logged in with their login
> name (in this case Edward), but have also made this login a 'db owner'
> so that when I created 2 new user-defined data types they belong to
> Edward rather than dbo.
> This must have happened a long time ago, but now that they want to
> move Edward round the roles and/or delete him from a copy of the
> database that they have, they can't because he's the owner of these
> user-defined types.
> This brings me to the reason for my post, how can I change the owner
> from Edward to dbo for these data types? I found an article on
> technet of how to do this, but when it suggests changing my
> user-defined type to standard format it doesn't seem to work.
> Any ideas?
> Rgds
> Robbiesql

Changing the owner of a user-defined data type

Hi Guys
Wonder if you could help me.
Basically I produce an accounts package that uses a SQL 2000 DB as the
RDBMS. I always instruct users to login as 'sa' and the relevant
password when doing an update to my program, as sometimes I need to do
database changes for new stuff.
Found that one of my users has not only logged in with their login
name (in this case Edward), but have also made this login a 'db owner'
so that when I created 2 new user-defined data types they belong to
Edward rather than dbo.
This must have happened a long time ago, but now that they want to
move Edward round the roles and/or delete him from a copy of the
database that they have, they can't because he's the owner of these
user-defined types.
This brings me to the reason for my post, how can I change the owner
from Edward to dbo for these data types? I found an article on
technet of how to do this, but when it suggests changing my
user-defined type to standard format it doesn't seem to work.
Any ideas?
Rgds
RobbieI have never changed the owner of a UDT, but if it can be done, then
sp_changeobjectowner would the be sp to do it.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robbie" <google@.astraaccounts.co.uk> wrote in message
news:f588d13a.0310062353.3dba1c6e@.posting.google.com...
> Hi Guys
> Wonder if you could help me.
> Basically I produce an accounts package that uses a SQL 2000 DB as the
> RDBMS. I always instruct users to login as 'sa' and the relevant
> password when doing an update to my program, as sometimes I need to do
> database changes for new stuff.
> Found that one of my users has not only logged in with their login
> name (in this case Edward), but have also made this login a 'db owner'
> so that when I created 2 new user-defined data types they belong to
> Edward rather than dbo.
> This must have happened a long time ago, but now that they want to
> move Edward round the roles and/or delete him from a copy of the
> database that they have, they can't because he's the owner of these
> user-defined types.
> This brings me to the reason for my post, how can I change the owner
> from Edward to dbo for these data types? I found an article on
> technet of how to do this, but when it suggests changing my
> user-defined type to standard format it doesn't seem to work.
> Any ideas?
> Rgds
> Robbie

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 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 Database.

I have SQL 2000, and I would like to change the owner of one of the DB? Can
you tell me how?
Thanks.sp_changedbowner
Russell Fields
"Hubco" <anonymous@.discussions.microsoft.com> wrote in message
news:DEE96313-0F74-4E75-9545-B2F4D55AECB0@.microsoft.com...
> I have SQL 2000, and I would like to change the owner of one of the DB?
Can you tell me how?
> Thanks.|||look at the system stored procedure sp_changedbowner in books online.
Ex:
EXEC sp_changedbowner '<new owner>'
Vishal Parkar
vgparkar@.yahoo.co.in|||Hi,
Please refer BOL for Sp_changedbowner system stored procedure
Thanks
Hari
MCDBA
"Hubco" <anonymous@.discussions.microsoft.com> wrote in message
news:DEE96313-0F74-4E75-9545-B2F4D55AECB0@.microsoft.com...
> I have SQL 2000, and I would like to change the owner of one of the DB?
Can you tell me how?
> Thanks.sql

Changing the owner of a Database.

I have SQL 2000, and I would like to change the owner of one of the DB? Can you tell me how?
Thanks.sp_changedbowner
Russell Fields
"Hubco" <anonymous@.discussions.microsoft.com> wrote in message
news:DEE96313-0F74-4E75-9545-B2F4D55AECB0@.microsoft.com...
> I have SQL 2000, and I would like to change the owner of one of the DB?
Can you tell me how?
> Thanks.|||look at the system stored procedure sp_changedbowner in books online.
Ex:
EXEC sp_changedbowner '<new owner>'
--
Vishal Parkar
vgparkar@.yahoo.co.in|||Hi,
Please refer BOL for Sp_changedbowner system stored procedure
Thanks
Hari
MCDBA
"Hubco" <anonymous@.discussions.microsoft.com> wrote in message
news:DEE96313-0F74-4E75-9545-B2F4D55AECB0@.microsoft.com...
> I have SQL 2000, and I would like to change the owner of one of the DB?
Can you tell me how?
> Thanks.

Changing the database Owner

Hi,
I have the problem changing the database onwer using sp_changedbowner. I
have a database TEST and owner it is shwing as userid/domain and in sysusers
in this database it showing sa as dbo. When I ran the sp_changedbowner sa ,
it is telling sa is already user in the database. I wnat to change this
database onwer to 'sa', can some tell how to do this.
Thnaks,
RbHow many rows does below return:
USE dbname
SELECT *
FROM sysusers su INNER JOIN master..syslogins AS sl ON su.sid = sl.sid
and sl.name = 'sa'
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"rb" <srbssr@.yahoo.com> wrote in message
news:uQ0A440uDHA.1224@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the problem changing the database onwer using sp_changedbowner. I
> have a database TEST and owner it is shwing as userid/domain and in
sysusers
> in this database it showing sa as dbo. When I ran the sp_changedbowner sa
,
> it is telling sa is already user in the database. I wnat to change this
> database onwer to 'sa', can some tell how to do this.
> Thnaks,
> Rb
>|||Tibor,
I am getting One row when I ran the query.
(1 row(s) affected)
Thanks,
RB
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23mYyRd1uDHA.3536@.tk2msftngp13.phx.gbl...
> How many rows does below return:
> USE dbname
> SELECT *
> FROM sysusers su INNER JOIN master..syslogins AS sl ON su.sid = sl.sid
> and sl.name = 'sa'
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "rb" <srbssr@.yahoo.com> wrote in message
> news:uQ0A440uDHA.1224@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > I have the problem changing the database onwer using sp_changedbowner. I
> > have a database TEST and owner it is shwing as userid/domain and in
> sysusers
> > in this database it showing sa as dbo. When I ran the sp_changedbowner
sa
> ,
> > it is telling sa is already user in the database. I wnat to change this
> > database onwer to 'sa', can some tell how to do this.
> >
> > Thnaks,
> > Rb
> >
> >
>|||Then sa is already a user in the database. What username does it say?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"rb" <srbssr@.yahoo.com> wrote in message
news:O0M9Gs2uDHA.2208@.TK2MSFTNGP10.phx.gbl...
> Tibor,
> I am getting One row when I ran the query.
> (1 row(s) affected)
> Thanks,
> RB
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:%23mYyRd1uDHA.3536@.tk2msftngp13.phx.gbl...
> > How many rows does below return:
> > USE dbname
> > SELECT *
> > FROM sysusers su INNER JOIN master..syslogins AS sl ON su.sid = sl.sid
> > and sl.name = 'sa'
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "rb" <srbssr@.yahoo.com> wrote in message
> > news:uQ0A440uDHA.1224@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > > I have the problem changing the database onwer using sp_changedbowner.
I
> > > have a database TEST and owner it is shwing as userid/domain and in
> > sysusers
> > > in this database it showing sa as dbo. When I ran the sp_changedbowner
> sa
> > ,
> > > it is telling sa is already user in the database. I wnat to change
this
> > > database onwer to 'sa', can some tell how to do this.
> > >
> > > Thnaks,
> > > Rb
> > >
> > >
> >
> >
>

changing table ownership

Did something change with the sp_changeobject owner with SQL2005. The
command does not seem to work. I am looking to change the owner of all the
tbale of a SQL2005 database. Is there a quick way to do it? Thanks.
Tom
ALTER SCHEMA targetschema TRANSFER sourceschema.Tblname;
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:umlac5lnIHA.5472@.TK2MSFTNGP03.phx.gbl...
> Did something change with the sp_changeobject owner with SQL2005. The
> command does not seem to work. I am looking to change the owner of all the
> tbale of a SQL2005 database. Is there a quick way to do it? Thanks.
>
|||Did you read the 2005 Books Online regarding this procedure? It should work, but since we now have
user-schema separation it is better to use the more modern what, where you can decide whether it is
the schema (ALTER SCHEMA) or the owner (ALTER AUTHORIZATION) to change.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:umlac5lnIHA.5472@.TK2MSFTNGP03.phx.gbl...
> Did something change with the sp_changeobject owner with SQL2005. The command does not seem to
> work. I am looking to change the owner of all the tbale of a SQL2005 database. Is there a quick
> way to do it? Thanks.
>
sql

changing table ownership

Did something change with the sp_changeobject owner with SQL2005. The
command does not seem to work. I am looking to change the owner of all the
tbale of a SQL2005 database. Is there a quick way to do it? Thanks.Tom
ALTER SCHEMA targetschema TRANSFER sourceschema.Tblname;
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:umlac5lnIHA.5472@.TK2MSFTNGP03.phx.gbl...
> Did something change with the sp_changeobject owner with SQL2005. The
> command does not seem to work. I am looking to change the owner of all the
> tbale of a SQL2005 database. Is there a quick way to do it? Thanks.
>|||Did you read the 2005 Books Online regarding this procedure? It should work, but since we now have
user-schema separation it is better to use the more modern what, where you can decide whether it is
the schema (ALTER SCHEMA) or the owner (ALTER AUTHORIZATION) to change.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:umlac5lnIHA.5472@.TK2MSFTNGP03.phx.gbl...
> Did something change with the sp_changeobject owner with SQL2005. The command does not seem to
> work. I am looking to change the owner of all the tbale of a SQL2005 database. Is there a quick
> way to do it? Thanks.
>

Changing table owner SQL database

i'm looking for a script to change the ownership of some tables in SQL
database from a certain user to dbo
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi.
You can utilize something as this:
sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'
This it is a stored procedure of SQL Server and only is able used by the use
rs in the roles SysAdmin and db_owner.
Hermilson Tinoco.
****************************************
*************
i'm looking for a script to change the ownership of some tables in SQL
database from a certain user to dbo|||Thanks for your reply.
Via other sources I've got a mail with the following script which
creates a stored procedure that you can execute to change the ownership
of several tables in one run:
CREATE PROC dbo.up_FixObjOwners
AS
SET NOCOUNT ON
DECLARE @.dynsql varchar(1000)
SET @.dynsql = ''
DECLARE @.Obj_Owner sysname
SET @.Obj_Owner = ''
DECLARE @.Obj_Type VARCHAR(30)
SET @.Obj_Type = ''
DECLARE @.Obj_Name sysname
SET @.Obj_Name = ''
DECLARE @.ObjCounter INT
SET @.ObjCounter = 0
DECLARE @.DBO CHAR(3)
SET @.DBO = 'DBO'
-- temp table to hold all objects not owned
-- by DBO
create table #ChangeOwners(
id int identity(1,1),
Obj_Owner sysname,
Obj_Name sysname,
Obj_Type varchar(30))
-- populate it
INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type)
select
su.name,
so.name,
case
when type = 'u' then 'table'
when type = 'p' then 'sproc'
when type = 'v' then 'view'
end as obj_type
from sysusers su
join sysobjects so
on su.uid = so.uid
where su.name not in ('information_schema', 'dbo')
and so.type in ('p', 'u', 'v')
-- select * from #ChangeOwners
SET @.ObjCounter = @.@.rowcount -- holds the count of rows inserted into
#ChangeOwners
WHILE @.Objcounter > 0
BEGIN
-- construct string for object ownership change
SELECT @.Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE
id = @.ObjCounter
SELECT @.Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @.ObjCounter
SET @.dynsql = 'sp_ChangeObjectOwner ''' + @.Obj_Name + ''', ' + @.DBO
--select @.dynsql
print 'changing ownership on ' + @.Obj_Type + ': ' + @.Obj_Name
EXEC(@.dynsql)
SET @.ObjCounter = @.ObjCounter - 1
END
-- ok all done, collect garbage
drop table #ChangeOwners
I hope u can use some other time too.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Note that changing the object owner (or renaming the proc) will not change
the underlying source text of the procedure. This can cause problems with
subsequent DDL scripting if the original owner was explicitly specified on
the CREATE statement.
Hope this helps.
Dan Guzman
SQL Server MVP
"stevesilent" <stevesilent@.devdex.com> wrote in message
news:OV1wf8J8DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> Via other sources I've got a mail with the following script which
> creates a stored procedure that you can execute to change the ownership
> of several tables in one run:
> CREATE PROC dbo.up_FixObjOwners
> AS
> SET NOCOUNT ON
> DECLARE @.dynsql varchar(1000)
> SET @.dynsql = ''
> DECLARE @.Obj_Owner sysname
> SET @.Obj_Owner = ''
> DECLARE @.Obj_Type VARCHAR(30)
> SET @.Obj_Type = ''
> DECLARE @.Obj_Name sysname
> SET @.Obj_Name = ''
> DECLARE @.ObjCounter INT
> SET @.ObjCounter = 0
> DECLARE @.DBO CHAR(3)
> SET @.DBO = 'DBO'
> -- temp table to hold all objects not owned
> -- by DBO
> create table #ChangeOwners(
> id int identity(1,1),
> Obj_Owner sysname,
> Obj_Name sysname,
> Obj_Type varchar(30))
> -- populate it
> INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type)
> select
> su.name,
> so.name,
> case
> when type = 'u' then 'table'
> when type = 'p' then 'sproc'
> when type = 'v' then 'view'
> end as obj_type
> from sysusers su
> join sysobjects so
> on su.uid = so.uid
> where su.name not in ('information_schema', 'dbo')
> and so.type in ('p', 'u', 'v')
> -- select * from #ChangeOwners
> SET @.ObjCounter = @.@.rowcount -- holds the count of rows inserted into
> #ChangeOwners
> WHILE @.Objcounter > 0
> BEGIN
> -- construct string for object ownership change
> SELECT @.Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE
> id = @.ObjCounter
> SELECT @.Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @.ObjCounter
> SET @.dynsql = 'sp_ChangeObjectOwner ''' + @.Obj_Name + ''', ' + @.DBO
> --select @.dynsql
> print 'changing ownership on ' + @.Obj_Type + ': ' + @.Obj_Name
> EXEC(@.dynsql)
> SET @.ObjCounter = @.ObjCounter - 1
> END
> -- ok all done, collect garbage
> drop table #ChangeOwners
>
> I hope u can use some other time too.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Changing Table Owner and user Owner

Hello there
I'm using Windows authentication, and i'm suddenly don't member of Dbo
owners.
What i need to change on the server in order so when i create new object
it's written as dbo and not as my name?
after i change that how can i change the owner of tables, views and store
procedures to be dbo owner?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilRoy,shalom
Pls read this article from BOL
Database Owner (dbo)
The dbo is a user that has implied permissions to perform all activities in
the database. Any member of the sysadmin fixed server role who uses a
database is mapped to the special user inside each database called dbo.
Also, any object created by any member of the sysadmin fixed server role
belongs to dbo automatically.
For example, if user Andrew is a member of the sysadmin fixed server role
and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as
Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed
server role but is a member only of the db_owner fixed database role and
creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The
table belongs to Andrew because he did not qualify the table as dbo.T1.
The dbo user cannot be deleted and is always present in every database.
Only objects created by members of the sysadmin fixed server role (or by the
dbo user) belong to dbo. Objects created by any other user who is not also a
member of the sysadmin fixed server role (including members of the db_owner
fixed database role):
a.. Belong to the user creating the object, not dbo.
b.. Are qualified with the name of the user who created the object.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OWTo$T54FHA.3188@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I'm using Windows authentication, and i'm suddenly don't member of Dbo
> owners.
> What i need to change on the server in order so when i create new object
> it's written as dbo and not as my name?
> after i change that how can i change the owner of tables, views and store
> procedures to be dbo owner?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>

Changing table owner

Hi- apologies for asking a stupid newbie question, but I'm really stuck at
the moment. I need to change table ownership.

I've got an asp script which is looking for a table owned by the dbo role,
however the table was created under a different ownership. I understand the
problem, and almost understand the solution, but I can't seem to get all the
way.

THE PROBLEM (using [server].[database].[owner].[table])
[mgbsvr1].[dnn].[dnnadmin].[aspsearch]
needs to be
[mgbsvr1].[dnn].[dbo].[aspsearch]

I looked up the books online and found this syntax:
sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'

But I can't see how to use it, nor more importantly, where I should use it.
It won't work in the query tool in Enterprise Manager. If I need to create a
script (which I've never done before), how do I execute the script?

All help deeply appreciated
Manning, SydneyHi

You will need to be connected to [mgbsvr1].[dnn] with either sysadmin,
db_ddladmin and db_securityadmin rights or the dbo to issue the statement

sp_changeobjectowner '[dnnadmin].[aspsearch]' , 'dbo'

John

"Manning" <manning@.NOSPAMbartlett.net> wrote in message
news:blcb0l$vk1$1@.lust.ihug.co.nz...
> Hi- apologies for asking a stupid newbie question, but I'm really stuck at
> the moment. I need to change table ownership.
> I've got an asp script which is looking for a table owned by the dbo role,
> however the table was created under a different ownership. I understand
the
> problem, and almost understand the solution, but I can't seem to get all
the
> way.
> THE PROBLEM (using [server].[database].[owner].[table])
> [mgbsvr1].[dnn].[dnnadmin].[aspsearch]
> needs to be
> [mgbsvr1].[dnn].[dbo].[aspsearch]
> I looked up the books online and found this syntax:
> sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'
> But I can't see how to use it, nor more importantly, where I should use
it.
> It won't work in the query tool in Enterprise Manager. If I need to create
a
> script (which I've never done before), how do I execute the script?
> All help deeply appreciated
> Manning, Sydney|||Manning (manning@.NOSPAMbartlett.net) writes:
> But I can't see how to use it, nor more importantly, where I should use
> it. It won't work in the query tool in Enterprise Manager. If I need to
> create a script (which I've never done before), how do I execute the
> script?

Enterprise Manager is not much of a query tool. Use Query Analyzer
instead.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||John and Erland

Thanks for your advice - problem fixed.

Manning

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f79bb7b$0$8769$ed9e5944@.reading.news.pipex.n et...
> Hi
> You will need to be connected to [mgbsvr1].[dnn] with either sysadmin,
> db_ddladmin and db_securityadmin rights or the dbo to issue the statement
> sp_changeobjectowner '[dnnadmin].[aspsearch]' , 'dbo'
> John

changing stored procedure ownership

Hi
I have many stored procedures which have the owner has 'essbase' e.g. Clear
Nominal, how do I change the ownership of the stored procedure(s) to dbo
Thanks
See sp_changeobjectowner in SQL Server Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:59E67253-2682-4D1C-9802-630B5F335BFC@.microsoft.com...
Hi
I have many stored procedures which have the owner has 'essbase' e.g. Clear
Nominal, how do I change the ownership of the stored procedure(s) to dbo
Thanks

changing stored procedure ownership

Hi
I have many stored procedures which have the owner has 'essbase' e.g. Clear
Nominal, how do I change the ownership of the stored procedure(s) to dbo
ThanksSee sp_changeobjectowner in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:59E67253-2682-4D1C-9802-630B5F335BFC@.microsoft.com...
Hi
I have many stored procedures which have the owner has 'essbase' e.g. Clear
Nominal, how do I change the ownership of the stored procedure(s) to dbo
Thankssql

Changing SP Owner

I maybe using this command incorrectly but here goes:
exec sp_changeobjectowner 'usp_get_blindedversion', 'dbo'.
I want to make this stored procedure created by a developer to owner=dbo.
I cannot get this to execute, the error is "Object usp_get_blindedversion
does not exist or is not a valid object for this operation.you probably need the full path of the sp, i.e.
developerslogin.usp_get_blindedversion
A
"Rich" wrote:

> I maybe using this command incorrectly but here goes:
> exec sp_changeobjectowner 'usp_get_blindedversion', 'dbo'.
> I want to make this stored procedure created by a developer to owner=dbo.
> I cannot get this to execute, the error is "Object usp_get_blindedversion
> does not exist or is not a valid object for this operation.
>|||Hmmmm, so you are thinking the developer's login should be included at the
begining of the name of the stored procedure, let me try that.
"bagman3rd" wrote:
> you probably need the full path of the sp, i.e.
> developerslogin.usp_get_blindedversion
> A
> "Rich" wrote:
>|||This works now however I see this
"Caution: Changing any part of an object name could break scripts and stored
procedures."
This appears to be just a warning, is that correct?
"bagman3rd" wrote:
> you probably need the full path of the sp, i.e.
> developerslogin.usp_get_blindedversion
> A
> "Rich" wrote:
>|||Rich,
You should check dependencies before changing the owner. If you are calling
this sp from others sps or client code, using owner_name.sp_name then after
changing the owner those calls will fail.
Example:
create procedure [test].[p1]
as
select 1 as c1
go
create procedure dbo.p2
as
exec [test].[p1]
go
exec dbo.p2
go
exec sp_depends '[test].[p1]'
go
exec sp_changeobjectowner '[test].[p1]', 'dbo'
go
exec dbo.p2
go
drop procedure [dbo].[p2], [dbo].[p1]
go
Result:
c1
--
1
(1 row(s) affected)
In the current database, the specified object is referenced by the following
:
name
name type
-- --
dbo.p2 stored procedure
Caution: Changing any part of an object name could break scripts and stored
procedures.
Server: Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'test.p1'.
AMB
"Rich" wrote:
> This works now however I see this
> "Caution: Changing any part of an object name could break scripts and stor
ed
> procedures."
> This appears to be just a warning, is that correct?
> "bagman3rd" wrote:
>|||Hi Rich
Object names are not necessarily unique, so if you don't specify an owner
for the proc, SQL Server won't be able to find it.
HTH
Kalen Delaney
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:25743607-5F7A-4BF0-B92E-161947AFCD03@.microsoft.com...
>I maybe using this command incorrectly but here goes:
> exec sp_changeobjectowner 'usp_get_blindedversion', 'dbo'.
> I want to make this stored procedure created by a developer to owner=dbo.
> I cannot get this to execute, the error is "Object usp_get_blindedversion
> does not exist or is not a valid object for this operation.
>

changing schema owner through SMO

I'm getting the can't drop user error. Is there a way to change the schema owner via smo? I've tried the following code to change the schema owner back to a different user. I don't get an error or exception but the schema owner doesn't change.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

Never mind, the following worked.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

database.Schemas["db_owner"].Alter();

|||

John, giving that your question is SMO related, I split it from the thread you posted it in and I moved it to the SMO forum.

Thanks
Laurentiu

Changing password of the Owner of the job

Hi,
I have created a job with specifying the owner as <SomeSQLLogin>.
Later I have changed the password of that login.
Do Impact of change of password will affect excution of job?
I have tried it 2-3 times but job is executing successfully.
But I am not fully convinced whether change of password affect the job execu
tion or not.
Can some give greater detail on this?
One more issue:
When executing the job I tried to capture login logout event in profiler. I
find that:
Login Event occurs under login name of Agent Service account.
Logout Event occurs for login name of owner of the job.
In between SQL Server somehow chaged the logged in user. I don't know how?
Please clear this issue also.
Thanks in advance
PushkarHi
This is from BOL regarding sp_start_job, that the owner is not who the
account under which the job is executed, but will restrict who can run the
job.
"Permissions
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the symin fixed role ca
n
start any job. A user who is not a member of the symin role can use
sp_start_job to start only the jobs he/she owns.
When sp_start_job is invoked by a user who is a member of the symin fixed
server role, sp_start_job will be executed under the security context in
which the SQL Server service is running. When the user is not a member of th
e
symin fixed server role, sp_start_job will impersonate the SQL Server
Agent proxy account, which is specified using xp_sqlagent_proxy_account. If
the proxy account is not available, sp_start_job will fail. This is only tru
e
for Microsoft? Windows NT? 4.0 and Windows 2000. On Windows 9.x, there is
no
impersonation and sp_start_job is always executed under the security context
of the Windows 9.x user who started SQL Server."
John
"Pushkar" wrote:

> Hi,
> I have created a job with specifying the owner as <SomeSQLLogin>.
> Later I have changed the password of that login.
> Do Impact of change of password will affect excution of job?
> I have tried it 2-3 times but job is executing successfully.
> But I am not fully convinced whether change of password affect the job exe
cution or not.
> Can some give greater detail on this?
> One more issue:
> When executing the job I tried to capture login logout event in profiler.
I find that:
> Login Event occurs under login name of Agent Service account.
> Logout Event occurs for login name of owner of the job.
> In between SQL Server somehow chaged the logged in user. I don't know how?
> Please clear this issue also.
> Thanks in advance
> Pushkar
>|||Hi,
I have not specified any account to be used as proxy account.
I have verified this by executing xp_sqlagent_proxy_account N'GET' and it
returns null.
But still job is running successfully.
I am not accessing any external resource through this job, then it does the
impersonation?
Is SQL Server is using 'sp_setuserbylogin' to change the logged on user from
Agent Service account to owner of the job.
Please clear my doubts.
Thanks
Pushkar
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A43E89C0-DE85-44D0-BBF9-867F5D822A38@.microsoft.com...
> Hi
> This is from BOL regarding sp_start_job, that the owner is not who the
> account under which the job is executed, but will restrict who can run the
> job.
> "Permissions
> Execute permissions default to the public role in the msdb database. A
> user
> who can execute this procedure and is a member of the symin fixed role
> can
> start any job. A user who is not a member of the symin role can use
> sp_start_job to start only the jobs he/she owns.
> When sp_start_job is invoked by a user who is a member of the symin
> fixed
> server role, sp_start_job will be executed under the security context in
> which the SQL Server service is running. When the user is not a member of
> the
> symin fixed server role, sp_start_job will impersonate the SQL Server
> Agent proxy account, which is specified using xp_sqlagent_proxy_account.
> If
> the proxy account is not available, sp_start_job will fail. This is only
> true
> for Microsoft Windows NT 4.0 and Windows 2000. On Windows 9.x, there is
> no
> impersonation and sp_start_job is always executed under the security
> context
> of the Windows 9.x user who started SQL Server."
> John
>
> "Pushkar" wrote:
>

Changing package's owner

Dear All,
Previosly we were using NT 4.0 domain called Domain_A. Recently we imployed
Active Directory and moved all domain accounts to a new domain, Domain_B. We
are using SQL Server 2000 database installed on Win 2K box. Programers are
connecting to SQL Server with trusted connection. When User_A (programer's
account) created a package, the owner of the package appeared as User_A.
Now, the new account of the same user is User_B. I changed owner in the
"owner" field in table "sysdtspackages", from User_A to User_B, so the owner
of the same package appears as User_B. Now, the problem is that User_B can
not save its own package - where owner is User_B, but previously was User_A.
Pops up a message that only sysadmins and owners can make changes to the
package (allthow User_B appears to be an owner).
Did anyone encountered this kind of problem?
Thanks in advance.
BilBil
Try
sp_reassign_dtspackageowner [@.name =] 'name',
[@.id =] 'id',
[@.newloginname =] 'newloginname'
"Bil" <biljanat@.nbrm.gov.mk> wrote in message
news:%23BepIEK0EHA.1204@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Previosly we were using NT 4.0 domain called Domain_A. Recently we
imployed
> Active Directory and moved all domain accounts to a new domain, Domain_B.
We
> are using SQL Server 2000 database installed on Win 2K box. Programers are
> connecting to SQL Server with trusted connection. When User_A (programer's
> account) created a package, the owner of the package appeared as User_A.
> Now, the new account of the same user is User_B. I changed owner in the
> "owner" field in table "sysdtspackages", from User_A to User_B, so the
owner
> of the same package appears as User_B. Now, the problem is that User_B can
> not save its own package - where owner is User_B, but previously was
User_A.
> Pops up a message that only sysadmins and owners can make changes to the
> package (allthow User_B appears to be an owner).
> Did anyone encountered this kind of problem?
> Thanks in advance.
> Bil
>|||Uri,
Thank you very much. This resolved my problem. This is undocumented stored
procedure.
Thanks a lot.
Regards,
Bil
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MHRBHK0EHA.3072@.TK2MSFTNGP11.phx.gbl...
> Bil
> Try
> sp_reassign_dtspackageowner [@.name =] 'name',
> [@.id =] 'id',
> [@.newloginname =] 'newloginname'
>
>
> "Bil" <biljanat@.nbrm.gov.mk> wrote in message
> news:%23BepIEK0EHA.1204@.TK2MSFTNGP10.phx.gbl...
> imployed
> We
> owner
> User_A.
>

Changing ownership of a SQL server 7 database

We recently changed the network administrator username on
the network where I work but all of our SQL 7 databases
still show the old name as the owner. How can this be
changed? thanks.Hi,
Use the below procedure in Query analyzer to change database owner,
sp_changedbowner 'Login_name'
Thanks
Hari
MCDBA
"Ken" <kklasser@.mindspring.com> wrote in message
news:e86601c3f070$3ad66b80$a601280a@.phx.gbl...
> We recently changed the network administrator username on
> the network where I work but all of our SQL 7 databases
> still show the old name as the owner. How can this be
> changed? thanks.