How to change DB owner

How to change DB owner

Post by Dann » Wed, 10 Oct 2001 06:46:59



Group,

I've got a few production databases that I would like to change the owner
from a NT Login to sa.  Normally I would use sp_changeDBowner but in these
cases it tells me that sa is already a user in the database.  I certainly
can not remove sa as a user because sa owns objects (ie system tables).  Any
suggestions?

Thanks,
Danny

 
 
 

How to change DB owner

Post by Kevin Connel » Wed, 10 Oct 2001 09:32:59


try changing it your id then back to sa


Quote:> Group,

> I've got a few production databases that I would like to change the owner
> from a NT Login to sa.  Normally I would use sp_changeDBowner but in these
> cases it tells me that sa is already a user in the database.  I certainly
> can not remove sa as a user because sa owns objects (ie system tables).
Any
> suggestions?

> Thanks,
> Danny


 
 
 

How to change DB owner

Post by Aja » Fri, 12 Oct 2001 17:11:11


try this :

For that database,
run select  name from sysusers

Then create a new login in SQLServer which doesnt exist in the above list.
Make the default database Master. Do not grant any server roles to the
login.  Do not grant any database access to the login.

use db
go
sp_changedbowner 'newlogin'
go
sp_changedbowner 'sa'

-Ajay.

This posting is provided AS IS with no warranties, and confers no rights.
You assume all risk for your use. ? 2001 Microsoft Corporation. All rights
reserved.

--------------------


>Newsgroups: microsoft.public.sqlserver.server
>Subject: How to change DB owner
>Lines: 12
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400

>NNTP-Posting-Host: 208.188.22.126

>X-Trace: newssvr16.news.prodigy.com 1002577619 ST000 208.188.22.126 (Mon,

08 Oct 2001 17:46:59 EDT)
>NNTP-Posting-Date: Mon, 08 Oct 2001 17:46:59 EDT
>Organization: Prodigy Internet http://www.prodigy.com
>X-UserInfo1:

\
_
BSY\G__IJIX_PLSA[CCFAULEY\FL\VLGANTQQ]FN
Quote:>Date: Mon, 08 Oct 2001 21:46:59 GMT
>Path:

cppssbbsa01.microsoft.com!news-out.cwix.com!newsfeed.cwix.com!newshub.northe
ast.verio.net!verio!nntp.abs.net!cpk-news-hub1.bbnplanet.com!news.gtei.net!n
ews.tele.dk!small.news.tele.dk!207.115.63.138!newscon04.news.prodigy.com!new
smst01.news.prodigy.com!prodigy.com!postmaster.news.prodigy.com!newssvr16.ne
ws.prodigy.com.POSTED!not-for-mail
Quote:>Xref: cppssbbsa01.microsoft.com microsoft.public.sqlserver.server:176483
>X-Tomcat-NG: microsoft.public.sqlserver.server

>Group,

>I've got a few production databases that I would like to change the owner
>from a NT Login to sa.  Normally I would use sp_changeDBowner but in these
>cases it tells me that sa is already a user in the database.  I certainly
>can not remove sa as a user because sa owns objects (ie system tables).  
Any
>suggestions?

>Thanks,
>Danny

 
 
 

1. cannot change DB-owner

Hello.

EXEC sp_changedbowner 'User_Name'

results in "Suggested Owner is already an user" (translated).

But: EXEC sp_dropuser 'User_Name'

gives the message: "User 'User_Name' does not exist in the current DB".

I suppose 'User_Name' formerly was an User in this DB and some kind of
error occured when he was dropped.

Does anyone know what to do?

Regards,

 Gauss

--
Posted via http://dbforums.com

2. Informix indexes

3. How to change DB owner

4. Index troubles with SQLLoader

5. Change DB owner/Table owner

6. callable statement please

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

8. Removing Maintenance Plan from a DB

9. Changing the DB owner

10. Changing all DB object owners to DBO

11. Change the owner of a DB?

12. DB Owner changed after Restore

13. change Db owner