ERP AI Agent Read-Only SQL Server Role Generator

-- In master
CREATE LOGIN [svc_ai_agent_ro] WITH PASSWORD = N'REPLACE_WITH_LONG_RANDOM_SECRET', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF, DEFAULT_DATABASE = [ERP_PROD];
GO
USE [ERP_PROD];
GO
CREATE USER [svc_ai_agent_ro] FOR LOGIN [svc_ai_agent_ro];
CREATE ROLE [ai_agent_readonly];
ALTER ROLE [ai_agent_readonly] ADD MEMBER [svc_ai_agent_ro];
GO

GRANT SELECT ON OBJECT::dbo.item_mst TO [ai_agent_readonly];
GRANT SELECT ON OBJECT::dbo.po_mst TO [ai_agent_readonly];

-- Hide sensitive columns even though the table is readable
DENY SELECT ON OBJECT::dbo.po_mst (vend_bank_acct, contact_email) TO [ai_agent_readonly];

-- Belt and braces: no writes, no execution, no schema changes
DENY INSERT, UPDATE, DELETE, EXECUTE, ALTER ON SCHEMA::dbo TO [ai_agent_readonly];
GO

CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_site_filter(@site AS nvarchar(30))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS allowed
WHERE IS_MEMBER(N'ai_agent_readonly') = 0 OR @site = N'PLANT1';
GO
CREATE SECURITY POLICY Security.ai_site_policy
ADD FILTER PREDICATE Security.fn_site_filter(site_ref) ON dbo.po_mst
WITH (STATE = ON);
GO

EXECUTE AS USER = N'svc_ai_agent_ro';
SELECT TOP (5) * FROM dbo.item_mst;
-- Expect a permission error on denied columns and on writes:
-- SELECT vend_bank_acct, contact_email FROM dbo.po_mst;
-- UPDATE dbo.item_mst SET 1 = 1;
REVERT;
SELECT permission_name, state_desc, OBJECT_NAME(major_id) AS obj FROM sys.database_permissions WHERE grantee_principal_id = USER_ID(N'ai_agent_readonly');