Least permission required to monitor sql server ,schema comapre, data modeler or query studio

-- ==========================================================================================
-- To Monitor SQL server below are list of required permission 
-- replace ServerName\UserName with your actual windows user or SQL Login
-- ==========================================================================================
USE master;
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'ServerName\UserName')
    CREATE LOGIN [ServerName\UserName] FROM WINDOWS;

GRANT CONNECT ANY DATABASE TO [ServerName\UserName];
GRANT VIEW SERVER STATE TO [ServerName\UserName];
GRANT SELECT ALL USER SECURABLES TO [ServerName\UserName];

DECLARE @major INT;
SELECT @major = CAST(SERVERPROPERTY('ProductMajorVersion') AS INT);

IF @major >= 16
BEGIN
    DECLARE @sql NVARCHAR(4000);
    SET @sql = N'GRANT VIEW SERVER PERFORMANCE STATE TO [ServerName\UserName]';
    EXEC(@sql);
END

GRANT EXECUTE ON xp_readerrorlog TO [ServerName\UserName];
GRANT ALTER ANY EVENT SESSION TO [ServerName\UserName];
GRANT VIEW ANY DEFINITION TO [ServerName\UserName];

USE msdb;
GRANT SELECT ON dbo.backupset TO [ServerName\UserName];
GRANT SELECT ON dbo.backupmediafamily TO [ServerName\UserName];

USE msdb;
ALTER ROLE SQLAgentReaderRole ADD MEMBER [ServerName\UserName];

-- END ==========================================================================================


-- ==========================================================================================





 
-- ==========================================================================================
-- FOR SQL Planner SQL SERVER SCHEMA COMPARISON & DEPLOYMENT ROLE CONFIGURATION 
-- , SQL Planner DATA Modeler, Query Studio 
-- ==========================================================================================

USE [master];
GO

-- 1. SERVER-LEVEL PRIVILEGES (Required for Encrypted Objects and Server Metadata)
-- Run this on the target instance if cross-database or system metadata analysis is needed.
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'YourServerLogin')
BEGIN
    -- CREATE LOGIN [YourServerLogin] WITH PASSWORD = 'StrongPassword123!';
    PRINT 'Ensure your login exists before proceeding.';
END;

GRANT VIEW ANY DEFINITION TO [YourServerLogin];
GRANT VIEW SERVER STATE TO [YourServerLogin]; -- Required for modern execution plan and dependency tracking
GO


USE [YourDatabase]; -- Run this inside BOTH your Source and Target databases
GO

-- 2. CREATE DATABASE ROLE FOR READ-ONLY SCHEMA COMPARISON
-- Ideal for developers who only need to run the comparison tool without changing anything.
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'db_schemacompare_reader' AND type = 'R')
BEGIN
    CREATE ROLE [db_schemacompare_reader];
END;

GRANT CONNECT TO [db_schemacompare_reader];
GRANT VIEW DEFINITION TO [db_schemacompare_reader];
GRANT SELECT ON sys.sql_expression_dependencies TO [db_schemacompare_reader]; -- Prevents engine timeouts on complex views
GRANT SELECT ON sys.columns TO [db_schemacompare_reader];
GRANT SELECT ON sys.objects TO [db_schemacompare_reader];
GRANT SELECT ON sys.types TO [db_schemacompare_reader];
GO


-- 3. CREATE DATABASE ROLE FOR FULL SCHEMA DEPLOYMENT (CONTAINS BOTH REFS AND REWRITES)
-- Ideal for Service Accounts or Deployment Pipelines (CI/CD) running the synchronisation.
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'db_schemacompare_deployer' AND type = 'R')
BEGIN
    CREATE ROLE [db_schemacompare_deployer];
END;

-- Explicitly assign DDL capabilities 
ALTER ROLE [db_ddladmin] ADD MEMBER [db_schemacompare_deployer];

-- Target structural dependencies often touch data or change system properties
-- Redgate and SSDT frequently need to select from/write to tables when altering columns (e.g., identity columns)
ALTER ROLE [db_datareader] ADD MEMBER [db_schemacompare_deployer];
ALTER ROLE [db_datawriter] ADD MEMBER [db_schemacompare_deployer];

-- Required for schema tools executing extended property updates or custom partition updates
GRANT ALTER TO [db_schemacompare_deployer]; 
GRANT VIEW DEFINITION TO [db_schemacompare_deployer];
GO


-- 4. ASSIGN ROLES TO YOUR TARGET USER
-- Replace 'YourDatabaseUser' with the user executing the tools (e.g., Redgate, SSDT, dbForge).

-- Option A: Assign this if they ONLY view comparisons
ALTER ROLE [db_schemacompare_reader] ADD MEMBER [YourDatabaseUser];

-- Option B: Assign this if they need to SYNC / APPLY changes to this database
-- ALTER ROLE [db_schemacompare_deployer] ADD MEMBER [YourDatabaseUser];
GO