MSDE and Database owners

How do you change the database owner in MSDE. When I try
and drop the current database owner, it gives me an error?
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:272101c51da2$45da2980$a601280a@.phx.gbl...
> How do you change the database owner in MSDE. When I try
> and drop the current database owner, it gives me an error?
Use sp_changedbowner stored procedure... you can't drop the current dbo
until they have been removed from all databases. Be careful that you don't
create "orphaned" objects by this action...
Steve
|||hi Peter,
Peter wrote:
> How do you change the database owner in MSDE. When I try
> and drop the current database owner, it gives me an error?
the user you want to transfer database ownership to, must not be already a
database user...
have a look at the following test script...
SET NOCOUNT ON
PRINT 'that''s me..'
SELECT SYSTEM_USER -- I'm logged with a trusted connection
PRINT ''
CREATE DATABASE test_user
GO
PRINT ''
USE test_user
PRINT 'who owns the database?'
EXEC sp_helpuser 'dbo'
EXEC sp_grantdbaccess 'roby' -- an additional user
PRINT ''
GO
PRINT 'this raises an error'
EXEC sp_changedbowner 'roby' , 'true'
PRINT ''
GO
PRINT 'this raises will run succesfully'
EXEC sp_revokedbaccess 'roby'
EXEC sp_changedbowner 'roby' , 'true'
PRINT ''
PRINT 'who owns the database?'
EXEC sp_helpuser 'dbo'
GO
USE master
DROP DATABASE test_user
in order to transfer the ownership to "roby" user, it's database access must
be first revoke and then it succedes...
please have a look at sp_changedbowner system stored procedure synopsis for
further info about it's use at
http://msdn.microsoft.com/library/en...ca-cz_30s2.asp or
Books On Line
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply