-- FuturisticREPO - 14 Hidden SQL Server Risks Discovery Procedures
-- Source: Hidden SQL Server Risks - A DBA Discovery Blog Series
-- DBAOps Futuristic Enterprise™
USE [FuturisticREPO];
GO

-- Table to store discovery query definitions (for collector to retrieve and run on targets)
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('discovery') AND name = 'DiscoveryDefinitions')
BEGIN
    CREATE TABLE discovery.DiscoveryDefinitions (
        DiscoveryID INT IDENTITY(1,1) NOT NULL,
        TypeCode NVARCHAR(50) NOT NULL,
        DisplayName NVARCHAR(200) NOT NULL,
        QueryText NVARCHAR(MAX) NOT NULL,
        RiskNumber INT NOT NULL,
        CONSTRAINT PK_DiscoveryDefinitions PRIMARY KEY (DiscoveryID)
    );
END
GO

DELETE FROM discovery.DiscoveryDefinitions;
INSERT INTO discovery.DiscoveryDefinitions (TypeCode, DisplayName, QueryText, RiskNumber) VALUES
('TDE','Databases Not Encrypted',
N'SELECT name AS DatabaseName, is_encrypted, CASE WHEN is_encrypted = 1 THEN ''Encrypted (TDE Enabled)'' ELSE ''Not Encrypted'' END AS EncryptionStatus FROM sys.databases ORDER BY name', 1),
('TDE_DETAILED','TDE Detailed Encryption Check',
N'SELECT db.name, dek.encryption_state, dek.encryptor_type, dek.key_algorithm, dek.key_length FROM sys.dm_database_encryption_keys dek JOIN sys.databases db ON dek.database_id = db.database_id', 1),
('BACKUP_STRATEGY','Backup Strategies Never Tested',
N'SELECT database_name, MAX(CASE WHEN type = ''D'' THEN backup_finish_date END) AS LastFullBackup, MAX(CASE WHEN type = ''I'' THEN backup_finish_date END) AS LastDiffBackup, MAX(CASE WHEN type = ''L'' THEN backup_finish_date END) AS LastLogBackup FROM msdb.dbo.backupset GROUP BY database_name ORDER BY database_name', 2),
('DATABASES_WITHOUT_BACKUP','Databases Without Backups',
N'SELECT name AS DatabaseName, recovery_model_desc, create_date FROM sys.databases d WHERE NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset b WHERE b.database_name = d.name) AND database_id > 4', 2),
('ORPHANED_USERS','Orphaned Database Users',
N'SELECT dp.name AS OrphanedUser, dp.type_desc, dp.authentication_type_desc FROM sys.database_principals dp LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid WHERE sp.sid IS NULL AND dp.type IN (''S'',''U'',''G'') AND dp.principal_id > 4', 3),
('REPLICATION','Replication Latency',
N'EXEC sp_replmonitorhelppublisher', 4),
('TEMPDB','TempDB Configuration',
N'SELECT name, physical_name, size * 8 / 1024 AS SizeMB FROM tempdb.sys.database_files', 5),
('TEMPDB_FILE_COUNT','TempDB File Count',
N'SELECT COUNT(*) AS TempDBFiles FROM tempdb.sys.database_files WHERE type_desc = ''ROWS''', 5),
('SA_ACCOUNT','Dangerous Defaults - SA',
N'SELECT name, is_disabled FROM sys.sql_logins WHERE name = ''sa''', 6),
('XP_CMDSHELL','Dangerous Defaults - xp_cmdshell',
N'EXEC sp_configure ''xp_cmdshell''', 6),
('DISK_SPACE','Disk Space Risks',
N'EXEC xp_fixeddrives', 7),
('FILE_GROWTH','Database File Growth',
N'SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalName, size * 8 / 1024 AS SizeMB, growth, is_percent_growth FROM sys.master_files ORDER BY DatabaseName', 7),
('SERVICE_ACCOUNTS','Overprivileged Service Accounts',
N'SELECT servicename, service_account FROM sys.dm_server_services', 8),
('JOB_OWNERS','SQL Agent Jobs Without Owners',
N'SELECT j.name AS JobName, SUSER_SNAME(j.owner_sid) AS JobOwner FROM msdb.dbo.sysjobs j ORDER BY JobName', 9),
('JOBS_OWNED_BY_SA','Jobs Owned by SA',
N'SELECT name FROM msdb.dbo.sysjobs WHERE SUSER_SNAME(owner_sid) = ''sa''', 9),
('CONNECTION_ENCRYPTION','Unencrypted Client Connections',
N'SELECT session_id, encrypt_option, auth_scheme, client_net_address FROM sys.dm_exec_connections WHERE session_id > 50', 10),
('LINKED_SERVERS','Hidden Linked Servers',
N'SELECT name, product, data_source, provider FROM sys.servers WHERE is_linked = 1', 11),
('JOB_DEPENDENCIES','Unknown SQL Server Dependencies',
N'SELECT j.name, s.step_name, LEFT(s.command, 200) AS command_preview FROM msdb.dbo.sysjobs j JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id ORDER BY j.name', 12),
('PATCH_DRIFT','SQL Server Patch Level Drift',
N'SELECT @@SERVERNAME AS ServerName, SERVERPROPERTY(''ProductVersion'') AS Version, SERVERPROPERTY(''ProductLevel'') AS PatchLevel, SERVERPROPERTY(''Edition'') AS Edition', 13),
('INTEGRITY_CHECKS','Missing Database Integrity Checks',
N'SELECT database_id, name, create_date FROM sys.databases WHERE database_id > 4', 14);
GO

-- Procedure to get discovery query by type
CREATE OR ALTER PROCEDURE discovery.usp_GetDiscoveryQuery
    @TypeCode NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT QueryText, DisplayName, RiskNumber
    FROM discovery.DiscoveryDefinitions
    WHERE TypeCode = @TypeCode;
END
GO

-- Procedure to insert discovery results (called by collector)
CREATE OR ALTER PROCEDURE discovery.usp_InsertDiscoveryResult
    @DiscoveryType NVARCHAR(100),
    @ServerName NVARCHAR(256),
    @DatabaseName NVARCHAR(256) = NULL,
    @Finding NVARCHAR(MAX),
    @Severity NVARCHAR(40) = 'Warning'
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO discovery.DiscoveryResults (DiscoveryType, ServerName, DatabaseName, Finding, Severity, Status)
    VALUES (@DiscoveryType, @ServerName, @DatabaseName, @Finding, @Severity, 'New');
END
GO

-- Procedure to sync ServerInventory from CMS (source of truth)
-- NOTE: Actual sync is performed by PowerShell using Get-DbaRegisteredServer -SqlInstance $CMSServer
-- and merging into config.ServerInventory. This procedure logs the sync request.
CREATE OR ALTER PROCEDURE config.usp_SyncFromCMS
    @CMSServer NVARCHAR(256)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO audit.AccessLog (EventType, ObjectName, Details)
    VALUES ('SyncFromCMS', @CMSServer, 'CMS sync requested - use PowerShell Get-DbaRegisteredServer to perform actual sync');
END
GO

PRINT 'FuturisticREPO discovery procedures created.';
GO
