Owership of database tables

Dear all,
I have a database in SQL server 2000. In Ent. Manager, it shows that the
"Owner" of the tables in this database is "dbo". Is it possible to change the
owner to one of the database user in this database?
Thanks in advance.
Ivan
Hi
If the user is called Albert:
EXEC sp_changedbowner 'Albert'
Just be aware, once a user is the owner of an object, you can not remove the
user until a new owner is set.
Regards
Mike
"Ivan" wrote:

> Dear all,
> I have a database in SQL server 2000. In Ent. Manager, it shows that the
> "Owner" of the tables in this database is "dbo". Is it possible to change the
> owner to one of the database user in this database?
> Thanks in advance.
> Ivan
|||Continued...
To change the owner of an object like a table or a SP:
EXEC sp_changeobjectowner 'authors', 'Albert'
If you change the owner of a View or Stored Procedure, it does not change
the actual script that is stored, so when you script the SP or view out, the
old owner is still shown on the Create statement.
Regards
Mike
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> If the user is called Albert:
> EXEC sp_changedbowner 'Albert'
> Just be aware, once a user is the owner of an object, you can not remove the
> user until a new owner is set.
> Regards
> Mike
> "Ivan" wrote:
|||Thanks for your help!
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Continued...
> To change the owner of an object like a table or a SP:
> EXEC sp_changeobjectowner 'authors', 'Albert'
> If you change the owner of a View or Stored Procedure, it does not change
> the actual script that is stored, so when you script the SP or view out, the
> old owner is still shown on the Create statement.
> Regards
> Mike
> "Mike Epprecht (SQL MVP)" wrote:

Owership of database tables

Dear all,
I have a database in SQL server 2000. In Ent. Manager, it shows that the
"Owner" of the tables in this database is "dbo". Is it possible to change th
e
owner to one of the database user in this database?
Thanks in advance.
IvanHi
If the user is called Albert:
EXEC sp_changedbowner 'Albert'
Just be aware, once a user is the owner of an object, you can not remove the
user until a new owner is set.
Regards
Mike
"Ivan" wrote:

> Dear all,
> I have a database in SQL server 2000. In Ent. Manager, it shows that the
> "Owner" of the tables in this database is "dbo". Is it possible to change
the
> owner to one of the database user in this database?
> Thanks in advance.
> Ivan|||Continued...
To change the owner of an object like a table or a SP:
EXEC sp_changeobjectowner 'authors', 'Albert'
If you change the owner of a View or Stored Procedure, it does not change
the actual script that is stored, so when you script the SP or view out, the
old owner is still shown on the Create statement.
Regards
Mike
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> If the user is called Albert:
> EXEC sp_changedbowner 'Albert'
> Just be aware, once a user is the owner of an object, you can not remove t
he
> user until a new owner is set.
> Regards
> Mike
> "Ivan" wrote:
>|||Thanks for your help!
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Continued...
> To change the owner of an object like a table or a SP:
> EXEC sp_changeobjectowner 'authors', 'Albert'
> If you change the owner of a View or Stored Procedure, it does not change
> the actual script that is stored, so when you script the SP or view out, t
he
> old owner is still shown on the Create statement.
> Regards
> Mike
> "Mike Epprecht (SQL MVP)" wrote:
>

Owership of database tables

Dear all,
I have a database in SQL server 2000. In Ent. Manager, it shows that the
"Owner" of the tables in this database is "dbo". Is it possible to change the
owner to one of the database user in this database?
Thanks in advance.
IvanHi
If the user is called Albert:
EXEC sp_changedbowner 'Albert'
Just be aware, once a user is the owner of an object, you can not remove the
user until a new owner is set.
Regards
Mike
"Ivan" wrote:
> Dear all,
> I have a database in SQL server 2000. In Ent. Manager, it shows that the
> "Owner" of the tables in this database is "dbo". Is it possible to change the
> owner to one of the database user in this database?
> Thanks in advance.
> Ivan|||Continued...
To change the owner of an object like a table or a SP:
EXEC sp_changeobjectowner 'authors', 'Albert'
If you change the owner of a View or Stored Procedure, it does not change
the actual script that is stored, so when you script the SP or view out, the
old owner is still shown on the Create statement.
Regards
Mike
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> If the user is called Albert:
> EXEC sp_changedbowner 'Albert'
> Just be aware, once a user is the owner of an object, you can not remove the
> user until a new owner is set.
> Regards
> Mike
> "Ivan" wrote:
> > Dear all,
> >
> > I have a database in SQL server 2000. In Ent. Manager, it shows that the
> > "Owner" of the tables in this database is "dbo". Is it possible to change the
> > owner to one of the database user in this database?
> > Thanks in advance.
> >
> > Ivan|||Thanks for your help!
"Mike Epprecht (SQL MVP)" wrote:
> Continued...
> To change the owner of an object like a table or a SP:
> EXEC sp_changeobjectowner 'authors', 'Albert'
> If you change the owner of a View or Stored Procedure, it does not change
> the actual script that is stored, so when you script the SP or view out, the
> old owner is still shown on the Create statement.
> Regards
> Mike
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > If the user is called Albert:
> >
> > EXEC sp_changedbowner 'Albert'
> >
> > Just be aware, once a user is the owner of an object, you can not remove the
> > user until a new owner is set.
> >
> > Regards
> > Mike
> >
> > "Ivan" wrote:
> >
> > > Dear all,
> > >
> > > I have a database in SQL server 2000. In Ent. Manager, it shows that the
> > > "Owner" of the tables in this database is "dbo". Is it possible to change the
> > > owner to one of the database user in this database?
> > > Thanks in advance.
> > >
> > > Ivan

Owership of database objects, Yu'll login to use?

I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
Sybase we would alias a developer to DBO (of that database only) so
that all objects (tables/stored procedures) would show up as owned by
DBO.
In SQL Server it looks to me that the only way to get obects owned by
DBO is to load them as SA or add the developer to the fixed role of
sysadmin. Neither of those appears to be the way to go.
So I'm wondering how other shows handle this issue. Do you set up an
specific account for loading objects and maintaining a SQL server
database?
Thanks,
Randy K
wawork@.hotmail.comHi Randy
I worked with Sybase for 8 years, and have been working with Microsoft SQL
Server since its inception.
In SQL Server, you can alias logins to the dbo user of a database exactly
the same way you do with Sybase, with the sp_addalias procedure.
Is it possible you're trying to achieve this functionality using the
db_owner role? Putting a user in that role will give her all the rights and
privileges of the database owner, but her name will still be her own name,
and not dbo. So any objects she creates, by default will not be owned by
dbo. However, someone in the db_owner role can create objects and specify
that the owner should be dbo:
CREATE TABLE dbo.newtable
(column ...)
So there are really two different things going on. You can actually give
someone the name dbo using sp_addalias, or you can put them in the db_owner
role, and they can specify that new obects are to be owned by dbo.
It's actually recommended that you always specify the owner name of objects,
both when creating them and when referencing them, and if you get into this
habit, you don't lose anything by just using the db_owner role.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Randy K" <wawork@.hotmail.com> wrote in message
news:3f27f6bc.59865578@.msnews.microsoft.com...
> I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
> Sybase we would alias a developer to DBO (of that database only) so
> that all objects (tables/stored procedures) would show up as owned by
> DBO.
> In SQL Server it looks to me that the only way to get obects owned by
> DBO is to load them as SA or add the developer to the fixed role of
> sysadmin. Neither of those appears to be the way to go.
> So I'm wondering how other shows handle this issue. Do you set up an
> specific account for loading objects and maintaining a SQL server
> database?
> Thanks,
> Randy K
> wawork@.hotmail.com|||Unfortunately, a user with the db_owner role can
> not change the ownership of a object when creating it using the ESRI
> software.
Yes, this can be a problem. Even Microsoft's own Enterprise Manager didn't
allow specifying a different owner in SQL Server 7, and although SQL 2000
allows it, it is not obvious how to do it. That's one of the reasons why I
never use GUIs to create tables. :-)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Randy K" <wawork@.hotmail.com> wrote in message
news:3f284734.18529640@.msnews.microsoft.com...
> Thank you Kalen that's what I was looking for. I had always used
> Sybase Central GUI to alias users as DBO and mistaking thought
> assigning a user to the db_owner role in SQL Server was the same
> thing.
> I'll need to do some testing since we are using SQL Server with ArcSDE
> by ESRI on top. Of the two methods only the first, spp_addalias,
> works with ArcSDE. Unfortunately, a user with the db_owner role can
> not change the ownership of a object when creating it using the ESRI
> software.
>
> On Wed, 30 Jul 2003 10:21:16 -0700, "Kalen Delaney"
> <replies@.public_newsgroups.com> wrote:
> >Hi Randy
> >
> >I worked with Sybase for 8 years, and have been working with Microsoft
SQL
> >Server since its inception.
> >
> >In SQL Server, you can alias logins to the dbo user of a database exactly
> >the same way you do with Sybase, with the sp_addalias procedure.
> >
> >Is it possible you're trying to achieve this functionality using the
> >db_owner role? Putting a user in that role will give her all the rights
and
> >privileges of the database owner, but her name will still be her own
name,
> >and not dbo. So any objects she creates, by default will not be owned by
> >dbo. However, someone in the db_owner role can create objects and specify
> >that the owner should be dbo:
> >
> >CREATE TABLE dbo.newtable
> >(column ...)
> >
> >So there are really two different things going on. You can actually give
> >someone the name dbo using sp_addalias, or you can put them in the
db_owner
> >role, and they can specify that new obects are to be owned by dbo.
> >
> >It's actually recommended that you always specify the owner name of
objects,
> >both when creating them and when referencing them, and if you get into
this
> >habit, you don't lose anything by just using the db_owner role.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Randy K" <wawork@.hotmail.com> wrote in message
> >news:3f27f6bc.59865578@.msnews.microsoft.com...
> >> I am very new to SQL Server 2000 but was a Sybase DBA for 2 years. In
> >> Sybase we would alias a developer to DBO (of that database only) so
> >> that all objects (tables/stored procedures) would show up as owned by
> >> DBO.
> >>
> >> In SQL Server it looks to me that the only way to get obects owned by
> >> DBO is to load them as SA or add the developer to the fixed role of
> >> sysadmin. Neither of those appears to be the way to go.
> >>
> >> So I'm wondering how other shows handle this issue. Do you set up an
> >> specific account for loading objects and maintaining a SQL server
> >> database?
> >>
> >> Thanks,
> >> Randy K
> >> wawork@.hotmail.com
> >
> >
>