SQL Server 點對點複寫配置失敗後無法刪除重建，報錯：Cannot execute as the database principal because the principal “

資料庫遷移慣用SSMS來進行視圖操作，但是在視圖遷移完成後進行點對點複寫的配置，到最後一步時報錯，訂閱的部分沒有成功建立。

如：

沒有辦法只能刪除點對點複寫並重建，但是在刪除時報錯如下：Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

在網上查了下資料並諮詢了下同事後來找到原來是微軟的bug：http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/91a27e23-c760-4ac1-80f4-0fac220c1adb/

This is a known issue when attaching a database through SSMS using a login which is not sysadmin, one hits this error.

The workaround for this currently is to use T-SQL to attach database .

CREATE DATABASE [databasename] ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\databasename.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\databasename_log.LDF' )

FOR ATTACH

執行下面的指令碼後可以刪除：

USE [xxxx]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO

對於複製的刪除可以通過視圖操作，也可以使用下面的指令碼：

ECLARE @publication AS sysname
DECLARE @publicationDB AS sysname
SET @publication = N'TOPUSA'
SET @publicationDB = N'TOPUSA'

-- Remove the publication.
USE [TOPUSA]
EXEC sp_droppublication @publication = @publication;

-- Remove replication objects from the database.
USE master
EXEC sp_droppublication
@dbname = @publicationDB,
@optname = N'publish',
@value = N'false'
GO

 

點對點複寫刪除的詳細操作步驟可以參照以下連結：http://msdn.microsoft.com/en-us/ms151256(SQL.90).aspx 。