change Db owner

change Db owner

Post by Steve Thompso » Sat, 12 Oct 2002 01:00:02




Quote:> I want to change the db owner for a database to a windows
> group that a found by executing sp_helplogins:

> sp_changedbowner 'domain\windowsusergroup'

> The query analyzer refuses, and says that the login
> doesn't exist. Is it not possible to make a winusergroup a
> dbo?

Groups will not work in this instance, you'll need to assign a database
"owner" that is a user login account.

Quote:> It is possible to put my WIN2000 login as the owner, could
> that cause any problem? What happens when I quit for
> instance.

You could, but then that will likely lead to referencing issues as all
objects in the database will owned under your account.  Broken or orphaned
ownership chains are common with this scenario. You may want to consider
keeping the owner as 'sa'...

Steve

 
 
 

change Db owner

Post by Kimberly L. Trip » Sun, 13 Oct 2002 11:07:44


Actually, any object created by THE dbo or the sa are automatically named as
DBO. Objects created by members of the db_owner role are named with the
individual's user name like Bob.object.

There's really no problem setting your account to be THE dbo using
sp_changedbowner but if you're only a member of the db_owner role then
you'll want to make sure that you always owner qualify your object create
statements like

    CREATE TABLE dbo.table
        ...

hth,
kt

Kimberly L. Tripp
********************
Please do not send mail to me directly - reply on the newsgroup.
Please include legible and tested code samples (and ddl if possible!). This
makes it easier to test and answer your questions. Thanks!




> > I want to change the db owner for a database to a windows
> > group that a found by executing sp_helplogins:

> > sp_changedbowner 'domain\windowsusergroup'

> > The query analyzer refuses, and says that the login
> > doesn't exist. Is it not possible to make a winusergroup a
> > dbo?

> Groups will not work in this instance, you'll need to assign a database
> "owner" that is a user login account.

> > It is possible to put my WIN2000 login as the owner, could
> > that cause any problem? What happens when I quit for
> > instance.

> You could, but then that will likely lead to referencing issues as all
> objects in the database will owned under your account.  Broken or orphaned
> ownership chains are common with this scenario. You may want to consider
> keeping the owner as 'sa'...

> Steve


 
 
 

change Db owner

Post by Rull » Wed, 16 Oct 2002 16:24:57


Ok, what I understand is that I better use the sa
as "default" owner. But what if I was only using "Windows
Authentication" security, what's then the solution to
avoid orphaned objects etc?

/Rulle

>-----Original Message-----


>> I want to change the db owner for a database to a
windows
>> group that a found by executing sp_helplogins:

>> sp_changedbowner 'domain\windowsusergroup'

>> The query analyzer refuses, and says that the login
>> doesn't exist. Is it not possible to make a
winusergroup a
>> dbo?

>Groups will not work in this instance, you'll need to
assign a database
>"owner" that is a user login account.

>> It is possible to put my WIN2000 login as the owner,
could
>> that cause any problem? What happens when I quit for
>> instance.

>You could, but then that will likely lead to referencing
issues as all
>objects in the database will owned under your account.  
Broken or orphaned
>ownership chains are common with this scenario. You may
want to consider
>keeping the owner as 'sa'...

>Steve

>.

 
 
 

change Db owner

Post by Dan Guzma » Wed, 16 Oct 2002 22:42:02


You can specify a standard SQL login (e.g. 'sa') regardless of the
authentication mode.  Since the 'sa' login must always exist, you will
never have problems with 'dbo' user mapping when your database is owned
by 'sa'.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


> Ok, what I understand is that I better use the sa
> as "default" owner. But what if I was only using "Windows
> Authentication" security, what's then the solution to
> avoid orphaned objects etc?

> /Rulle

> >-----Original Message-----


> >> I want to change the db owner for a database to a
> windows
> >> group that a found by executing sp_helplogins:

> >> sp_changedbowner 'domain\windowsusergroup'

> >> The query analyzer refuses, and says that the login
> >> doesn't exist. Is it not possible to make a
> winusergroup a
> >> dbo?

> >Groups will not work in this instance, you'll need to
> assign a database
> >"owner" that is a user login account.

> >> It is possible to put my WIN2000 login as the owner,
> could
> >> that cause any problem? What happens when I quit for
> >> instance.

> >You could, but then that will likely lead to referencing
> issues as all
> >objects in the database will owned under your account.
> Broken or orphaned
> >ownership chains are common with this scenario. You may
> want to consider
> >keeping the owner as 'sa'...

> >Steve

> >.

 
 
 

1. Change DB owner/Table owner

I know it is possible to change the database owner, but am I able to
change
the owner of a table as well?  Currently, the owner of the database and
tables
is EXAV.  The problem is for the programmers...  this is the only table
owned by
anyone other than DBO.  We've got some code that is supposed to work
for both tables and it causes them some coding problems because that
qualifier
isn't the same.  For the tables owned by EXAV, they have to code it
EXAV.tablename
and for the tables owned by DBO, they just code the tablename.

Is there anything I can do short of creating a new database and copying
the data over?
Thanks for the advice!
Toni Eibner

2. DTSLookups type mismatch error

3. change db owner from sa to another login with sysadmin provilege

4. PDOXDOS: Numerical Field Width

5. How to change DB owner

6. How to copy a record to a clone database by using SQL

7. No Current Row problem

8. cannot change DB-owner

9. DB Owner changed after Restore

10. Change the owner of a DB?

11. Changing all DB object owners to DBO

12. Changing the DB owner