Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The proposed new database owner is already a user or aliased in the database Expand / Collapse
Author
Message
Posted Tuesday, February 23, 2010 2:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 10:33 AM
Points: 282, Visits: 757
Hi,

We have BizTalk databases on SQL Server 2005 EE x64 with SP3. For security reason, we have changed the BizTalk databases owner to SA from abc\btsdadmin. But from BizTalk best practices, we came to know that we should NOT change the database owner from BTSAdmin. So I want to change the owner back to abc\BTSAdmin. I'm executing the below query:

Use BizTalkMsgBoxDb
GO
Exec sp_changedbowner 'abc\btsdadmin'


But I'm getting the below error:

Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.


Please advice me on how can I change the database owner form SA to abc\btsdadmin


Post #871484
Posted Tuesday, February 23, 2010 3:02 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
Drop the user named 'abc\btsdadmin' from this database first and then add it again by using sp_changedbowner command.
Post #871493
Posted Tuesday, February 23, 2010 3:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 10:33 AM
Points: 282, Visits: 757
thanks Manu,

But after changing the owner to abc\btsadmin, I want to have the below database roles (these roles already exist now, with database owner SA and I want to keep them as it is after changing the owner to abc\btsadmin)

db_accessadmin

db_ddladmin

db_securityadmin

public

please see the attachment


  Post Attachments 
BizTalk_Owner.docx (26 views, 26.46 KB)
Post #871496
Posted Wednesday, February 24, 2010 12:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 10:33 AM
Points: 282, Visits: 757
I just need to drop the user and change the owner and map the database roles again right?
Post #872157
Posted Wednesday, February 24, 2010 12:14 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:15 PM
Points: 5,976, Visits: 12,886
if you make abc\btsadmin the owner of the database it will have all other rights by default.

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

Post #872172
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse