datahub/metadata-ingestion/docs/sources/mssql/mssql_pre.md

Prerequisites

If you want to ingest MSSQL Jobs and stored procedures (with code) the user credentials needs the proper privileges.

The DataHub MSSQL source automatically detects your environment and uses the optimal method:

• RDS/Managed SQL Server: Uses stored procedures (recommended for managed environments)
• On-premises SQL Server: Uses direct table access (typically faster when available)
• Automatic fallback: If the primary method fails, it automatically tries the alternative approach

Permissions for All Environments

-- Core permissions for stored procedures (required)
USE MSDB
GRANT SELECT ON OBJECT::msdb.dbo.sysjobsteps TO 'USERNAME'
GRANT SELECT ON OBJECT::msdb.dbo.sysjobs TO 'USERNAME'

-- Stored procedure permissions (required for RDS/managed environments)
GRANT EXECUTE ON msdb.dbo.sp_help_job TO 'USERNAME'
GRANT EXECUTE ON msdb.dbo.sp_help_jobstep TO 'USERNAME'

-- Permissions for stored procedure code and lineage
USE 'DATA_DB_NAME'
GRANT VIEW DEFINITION TO 'USERNAME'
GRANT SELECT ON OBJECT::sys.sql_expression_dependencies TO 'USERNAME'

RDS SQL Server Specific Notes

For Amazon RDS SQL Server environments, the stored procedure approach is preferred and typically the only method that works due to restricted table access. Ensure the following permissions are granted:

-- Essential for RDS environments
GRANT EXECUTE ON msdb.dbo.sp_help_job TO 'USERNAME'
GRANT EXECUTE ON msdb.dbo.sp_help_jobstep TO 'USERNAME'

On-Premises SQL Server Notes

For on-premises SQL Server installations, direct table access is typically available and faster. The source will automatically use direct queries when possible, with stored procedures as fallback.

Troubleshooting Permissions

If you encounter permission errors:

1. RDS environments: Ensure stored procedure execute permissions are granted
2. On-premises environments: Verify both table select and stored procedure execute permissions
3. Mixed environments: Grant all permissions listed above for maximum compatibility

The DataHub source will automatically handle fallback between methods and provide detailed error messages with specific permission requirements if issues occur.