# ERP AI Agent Read-Only SQL Server Role Generator

> Source: https://netray.co/tools/erp-ai-agent-read-only-sql-role-generator-sql-server
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Create a least-privilege SQL Server login and role for an AI agent, with table grants, sensitive column denies and a row filter by site.

## Category

developer

## Audience

- DBA
- ERP administrator
- IT manager
- Security engineer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### ERP database name

**Key:** db

**Type:** text

**Default:** ERP_PROD

#### Login name

**Key:** login

**Type:** text

**Default:** svc_ai_agent_ro

#### Role name

**Key:** role

**Type:** text

**Default:** ai_agent_readonly

#### Table or view 1 (schema.name)

**Key:** table1

**Type:** text

**Default:** dbo.item_mst

#### Table or view 2 (schema.name)

**Key:** table2

**Type:** text

**Default:** dbo.po_mst

#### Sensitive columns to deny on table 2

**Key:** secret_cols

**Type:** text

**Default:** vend_bank_acct, contact_email

#### Table for site row filter

**Key:** filter_table

**Type:** text

**Default:** dbo.po_mst

#### Site column name

**Key:** site_col

**Type:** text

**Default:** site_ref

#### Allowed site value

**Key:** site

**Type:** text

**Default:** PLANT1

**Blocks:**

#### 1. Login, user and role (run in master, then in the ERP database)

**Body:** -- In master
CREATE LOGIN [{{login}}] WITH PASSWORD = N'REPLACE_WITH_LONG_RANDOM_SECRET', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF, DEFAULT_DATABASE = [{{db}}];
GO
USE [{{db}}];
GO
CREATE USER [{{login}}] FOR LOGIN [{{login}}];
CREATE ROLE [{{role}}];
ALTER ROLE [{{role}}] ADD MEMBER [{{login}}];
GO

#### 2. Grants and explicit denies

**Body:** GRANT SELECT ON OBJECT::{{table1}} TO [{{role}}];
GRANT SELECT ON OBJECT::{{table2}} TO [{{role}}];

-- Hide sensitive columns even though the table is readable
DENY SELECT ON OBJECT::{{table2}} ({{secret_cols}}) TO [{{role}}];

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

#### 3. Row-level security by site

**Body:** 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'{{role}}') = 0 OR @site = N'{{site}}';
GO
CREATE SECURITY POLICY Security.ai_site_policy
ADD FILTER PREDICATE Security.fn_site_filter({{site_col}}) ON {{filter_table}}
WITH (STATE = ON);
GO

#### 4. Verify as the agent login

**Body:** EXECUTE AS USER = N'{{login}}';
SELECT TOP (5) * FROM {{table1}};
-- Expect a permission error on denied columns and on writes:
-- SELECT {{secret_cols}} FROM {{table2}};
-- UPDATE {{table1}} 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'{{role}}');

**Notes:** Table and column names shown are examples; replace them with your ERP schema. Test on a non-production copy first. Row-level security needs SQL Server 2016 or later, and the filter column type in the function must match the table column. Rotate the password and store it in a secrets manager. Add database audit if your policy requires reads to be logged.

## Content

**Intro:** An AI agent should never connect to your ERP database with a powerful account. This generator writes the T-SQL for a dedicated login, a read-only role, column-level denies and a row filter so the agent can only see what its job requires.

**Sections:**

#### How to use it

**Body:** Replace the table and column names with real objects from your ERP, preferably views that already expose only reporting fields. Run block one in master and the ERP database, grant only the objects the agent needs in block two, add the optional row filter in block three, and then run the verification block as the agent. If a write succeeds, stop and review the grants before connecting any AI tooling.

#### Why layers matter

**Body:** A prompt that says do not modify data is not a control, because a model can be manipulated by hostile text in a record. The database enforces the limit instead. Grants define what can be read, column denies protect bank details and personal data, and row-level security keeps an agent for one plant away from another plant. A read-only agent also limits blast radius if a tool is misused.

**Bullets:**

- Grant on views, not base tables, where possible
- Deny sensitive columns explicitly
- Filter rows by site or company
- Log reads where policy demands it

#### Common mistakes

**Body:** Adding the login to db_datareader gives access to every table, including payroll or banking data. Reusing the ERP application account bypasses your own role design. Forgetting to test as the login hides gaps. Some ERP vendors do not support direct database changes or objects in their schema, so confirm with the vendor and keep your objects in a separate schema.

**Cta:** Netray builds read-only ERP agents that show the query they ran and respect your roles. We can review the database account design for your system.

**Faq:**

#### Should the agent query base tables or views?

**Answer:** Views are safer. They let you expose only the columns and rows the agent needs and keep it working when the base schema changes. Grant select on the views and nothing else.

#### Does DENY override GRANT?

**Answer:** Yes, in SQL Server a DENY takes precedence over a GRANT, which is why a column-level DENY works even when the table is otherwise readable by the role.

#### Will this affect ERP upgrades?

**Answer:** Possibly. Security policies with schema binding can block certain schema changes on the filtered table. Test with your vendor upgrade process, or prefer filtering inside a view.

#### Does this work for Oracle or PostgreSQL ERPs?

**Answer:** The pattern is the same (dedicated user, read-only role, column and row controls) but the syntax differs. This generator produces T-SQL only.

## Search terms

sql server read only user for reporting, create read only role sql server, deny select column sql server, row level security sql server example, least privilege database account ai agent, syteline read only sql account, grant select on specific tables sql server

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json