Skip to main content

Required Privileges for SQL Server

Overview​

BladePipe needs some permissions to be granted for the account when doing the data migration synchronization with SQL Server as Source/Target. If you are using a SQL Server account that already has DBA/SA permissions when adding a DataSource, you can ignore the following part.

Account Creation​

You can skip this step if you already have an account ready for data synchronization

  1. Create a bladepipe login account to connect to the database.

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

  2. Authorized bladepipe login account can connect to the database.

    GRANT CONNECT SQL TO [bladepipe]

As the Source​

  1. Switch to the xxx source database to prepare for the next assignment of database users.

    USE [xxx]

  2. Assign database users with the same name to bladepipe login accounts.

    CREATE USER [bladepipe] FOR LOGIN [bladepipe]

  3. Enable CDC for xxx data, this command requires the sysadmin server role and should be operated by the DBA alone.

    exec [xxx].sys.sp_cdc_enable_db

  4. Assign the db_owner identity to the bladepipe login account, which is required by the new task to create the CDC table.

    ALTER ROLE [db_owner] ADD MEMBER [bladepipe]

As the Target​

If you already have the db_owner identity of the xxx database, the following authorization actions are not required.

  1. Switch to the xxx target database to prepare for the next assignment of database users.

    USE [xxx]

  2. Assign database users with the same name to bladepipe login accounts.

    CREATE USER [bladepipe] FOR LOGIN [bladepipe]

  3. Create a table structure on the opposite side during schema migration.

    GRANT CREATE TABLE TO [bladepipe]
    GRANT ALTER TO [bladepipe]

  4. Set table/column remark information during schema migration, and synchronize source table/column renamed DDL during incremental DDL synchronization.

    GRANT EXECUTE TO [bladepipe]

  5. Grant INSERT, UPDATE, DELETE permissions.

    GRANT INSERT TO [bladepipe]
    GRANT UPDATE TO [bladepipe]
    GRANT DELETE TO [bladepipe]

Schema-Level Permission​

If you already have the db_owner identity for the xxx database, you do not need the following authorization.

Single SCHEMA Permission

  1. Create tables under SCHEMA.

    GRANT ALTER ON SCHEMA::[my_schema] TO [bladepipe]
    GRANT EXECUTE ON SCHEMA::[my_schema] TO [bladepipe]

  2. Grant INSERT, UPDATE, DELETE permissions.

    GRANT INSERT ON SCHEMA::[my_schema] TO [bladepipe]
    GRANT UPDATE ON SCHEMA::[my_schema] TO [bladepipe]
    GRANT DELETE ON SCHEMA::[my_schema] TO [bladepipe]

Whole SCHEMA Permission

  1. Create tables under SCHEMA.

    GRANT ALTER ON SCHEMA::* TO [bladepipe]
    GRANT EXECUTE ON SCHEMA::* TO [bladepipe]

  2. Grant INSERT, UPDATE, DELETE permissions.

    GRANT INSERT ON SCHEMA::* TO [bladepipe]
    GRANT UPDATE ON SCHEMA::* TO [bladepipe]
    GRANT DELETE ON SCHEMA::* TO [bladepipe]