Connect SQL Server

-- Create login and user
CREATE LOGIN unitylayer_user WITH PASSWORD = '<YOUR_SECURE_PASSWORD>';
CREATE USER unitylayer_user FOR LOGIN unitylayer_user;

-- Grant permissions to view metadata and access schemas
USE [YourDatabase];
GO

GRANT VIEW DEFINITION TO unitylayer_user;
GRANT SELECT ON SCHEMA::dbo TO unitylayer_user;
-- Repeat for other schemas as needed
GRANT SELECT ON SCHEMA::your_schema_name TO unitylayer_user;

-- Create synonyms for easier access (optional)
DECLARE @create_sql NVARCHAR(MAX) = N'';
SELECT @create_sql += '
CREATE SYNONYM ' + QUOTENAME(LOWER(t.name)) + '
FOR ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = 'YOUR_SCHEMA_NAME';
EXEC sp_executesql @create_sql;

-- Enable change tracking (optional)
ALTER DATABASE YourDatabase SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

-- Enable change tracking on specific tables
ALTER TABLE dbo.YourTable ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

-- Ensure firewall allows access:
-- - For Azure SQL: Configure firewall rules in Azure Portal
-- - For on-premises: Ensure firewall allows connections on port 1433 (default)
-- 
-- Verify connectivity from your ingestion environment