主页 / user-39145

Dave.Gugg's questions

Martin Hope
Dave.Gugg
Asked: 2024-08-16 02:17:26 +0800 CST
  • 5

我正在通过每晚备份我们的 Prod 环境并将其应用到我们的新 PreProd 环境来设置 Staging/PreProd 环境。这部分运行良好。不幸的是，我们的环境中设置了大量复制。我已经设置了过程以在每晚恢复之前删除 PreProd 复制，然后在恢复完成后重新创建它。这可以正常工作，但是需要很长时间（3-4 小时），因为有些表很大。我想压缩这些大表，但我不想使工作量翻倍。应用快照的第一步是删除并重新创建目标表。第二步是插入所有数据。我可以在之后立即压缩，但这样我基本上要构建两次表，第一次不压缩，第二次压缩。

我已经测试了 @pre_snapshot_script 参数，但它在删除并重新创建表（Snapshot 文件夹中的 .pre 和 .sch 文件）的步骤之前应用。这不起作用。

有什么方法可以通过编程确保在将快照数据应用到订阅者表之前对其进行压缩？

sql-server
Martin Hope
Dave.Gugg
Asked: 2017-01-14 14:39:03 +0800 CST
  • 11

我使用跨数据库证书（如 Erland Sommarskog 所解释的）来控制对我的环境（SQL Server 2008 R2）中某个数据库的访问。

我在数据库 A 中存储了更新数据库 B 中表的存储过程。到目前为止，这一直适用于 db A 中的各种存储过程和 db B 中的表。我正在尝试更新 db B 中的表，但该表上有一个触发器。此触发器正在 db B 的另一个表中插入其他数据。我收到错误消息：

消息 916，级别 14，状态 1，过程 table_trigger，第 11 行 服务器主体“sql\login”无法在当前安全上下文下访问数据库“B”。

我尝试为与证书绑定的数据库 B 用户授予插入权限以插入到另一个表中，但它没有解决错误。除了更改触发器以使其使用之外，我还有其他选择WITH EXECUTE AS OWNER吗？

这是复制问题的 DDL：

CREATE LOGIN [GuggTest] WITH PASSWORD=N'abcd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE DATABASE A;
CREATE DATABASE B;

USE A;

CREATE TABLE dbo.SPtoUpdate
    (
      ID INT
    , ILoveFishing VARCHAR(255)
    );
INSERT INTO dbo.SPtoUpdate
        ( ID , ILoveFishing )
VALUES  ( 1,'Musky'),( 2,'Pike'),( 3,'Yellow Perch');
CREATE TABLE dbo.TriggerToInsert
    (
      ID INT
    , ILoveFishing VARCHAR(255)
    , ChangeDate DATETIME2
    );
GO

CREATE TRIGGER dbo.SPtoUpdateTrigger ON dbo.SPtoUpdate
    FOR UPDATE
AS
    DECLARE @datetime DATETIME2;
    SELECT  @datetime = GETDATE()

    INSERT  INTO dbo.TriggerToInsert
            ( ID , ILoveFishing , ChangeDate )
    VALUES  ( 1 , 'Yes' , @datetime );
GO

CREATE CERTIFICATE BExecutor
   ENCRYPTION BY PASSWORD = 'Obfuscated'
   WITH SUBJECT = 'Execute sp from B to A',
   START_DATE = '20140101', EXPIRY_DATE = '20300101'
GO

BACKUP CERTIFICATE BExecutor TO FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'Obfuscated',
                  DECRYPTION BY PASSWORD = 'Obfuscated')
GO

CREATE USER BExecutor FROM CERTIFICATE BExecutor

GRANT UPDATE ON dbo.SPtoUpdate TO BExecutor
GRANT SELECT ON dbo.SPtoUpdate TO BExecutor
--Also give insert on dbo.TriggerToInsert
GRANT INSERT ON dbo.TriggerToInsert TO BExecutor

USE B
GO

CREATE USER [GuggTest] FOR LOGIN [GuggTest];
EXEC sp_addrolemember N'db_owner', N'GuggTest'
GO

CREATE PROCEDURE dbo.UpdateTableInA
AS
    BEGIN
        UPDATE  A.dbo.SPtoUpdate
        SET     ILoveFishing = 'Walleye'
        WHERE   ID = 2;
    END

GO


CREATE CERTIFICATE BExecutor FROM FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'Obfuscated',
                  DECRYPTION BY PASSWORD = 'Obfuscated')
GO

EXEC master..xp_cmdshell 'DEL C:\temp\crossdbcert.*', 'no_output'
GO

ADD SIGNATURE TO dbo.UpdateTableInA BY CERTIFICATE BExecutor
    WITH PASSWORD = 'Obfuscated'
GO

--Log In or Change execution context to GuggTest, then EXEC dbo.UpdateTableInA
sql-server permissions