sql server msde: How do you change the database owner in MSDE. When I try

[quoted text, click to view]

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,
[quoted text, click to view]

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-us/tsqlref/ts_sp_ca-cz_30s2.asp or
Books On Line
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://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