咨询AWS DMS迁移本地SQL Server至RDS现有数据的简化权限方案

Great question—since you only need a one-time full migration (no ongoing change data capture), you absolutely have options to avoid granting sysadmin or excessive permissions to DMS or other AWS tools. Here are the most practical approaches tailored to your scenario:

While DMS documentation often references sysadmin for SQL Server sources, that requirement is primarily for change data capture (CDC) scenarios. For a full-only migration, you can lock down permissions to exactly what's needed:

• Grant SELECT on all tables/views you plan to migrate (the core requirement for accessing your data)
• Grant VIEW DEFINITION on the source database (to let DMS read schema metadata like table structures and data types)
• Grant SELECT on key system catalog views: sys.tables, sys.columns, sys.schemas, sys.data_spaces, sys.indexes, sys.index_columns
• Optional: If using table filters, add SELECT on sys.partitions

You can apply these permissions with a script like this:

-- Replace placeholders with your actual database and user names
USE YOUR_SOURCE_DATABASE;
GRANT SELECT ON ALL TABLES IN SCHEMA dbo TO YOUR_DMS_SERVICE_USER; -- Adjust schemas as needed
GRANT VIEW DEFINITION ON DATABASE::YOUR_SOURCE_DATABASE TO YOUR_DMS_SERVICE_USER;
GRANT SELECT ON sys.tables TO YOUR_DMS_SERVICE_USER;
GRANT SELECT ON sys.columns TO YOUR_DMS_SERVICE_USER;
GRANT SELECT ON sys.schemas TO YOUR_DMS_SERVICE_USER;
GRANT SELECT ON sys.data_spaces TO YOUR_DMS_SERVICE_USER;
GRANT SELECT ON sys.indexes TO YOUR_DMS_SERVICE_USER;
GRANT SELECT ON sys.index_columns TO YOUR_DMS_SERVICE_USER;

This avoids sysadmin entirely, and you won't need any transaction log permissions since you're not tracking ongoing changes.

If you want even fewer permissions (only SELECT on your tables), this low-overhead approach works well:

• Step 1: Use the AWS Schema Conversion Tool (SCT) to validate your source schema against RDS SQL Server (since it's the same engine, this is mostly for checking RDS-specific compatibility limits)
• Step 2: Export your data locally using SQL Server's bcp utility or SSMS's "Export Data" wizard—both only require SELECT access to your tables
• Step 3: Upload the exported files (CSV or BCP format) to an S3 bucket
• Step 4: Import the data into your RDS SQL Server instance using bcp (connecting directly to RDS) or SSMS's "Import Data" wizard pointing to the S3 files (RDS also supports direct imports from S3 via the console/CLI)

This is ideal for smaller datasets, and you don't have to configure any DMS permissions at all.

For larger databases, this is often the fastest method, and it only requires backup-related permissions on the source:

• Step 1: Take a full backup of your source SQL Server database (you'll need the BACKUP DATABASE permission)
• Step 2: Upload the backup file to an S3 bucket
• Step 3: Use the AWS Console or CLI to restore the backup directly to your RDS SQL Server instance (RDS natively supports restoring SQL Server backups from S3)

You just need BACKUP DATABASE on the source, plus access to write the backup file to a location you can upload to S3. No DMS setup is required, and permissions are kept minimal.

• DMS全量迁移: Best if you want an automated, managed pipeline (no manual file handling) and can grant the minimal catalog permissions
• SCT + 导出/导入: Best for the smallest permission footprint (only SELECT) and simple datasets
• 备份/还原: Best for large databases, fastest transfer speeds, and a straightforward workflow

内容的提问来源于stack exchange，提问作者wrschneider