Limit SQL Agent to Read-Only Queries and Approved Tables

This guide shows you how to configure a SQL Server Agent job or a custom application so that it can only run SELECT queries against a specific set of approved tables. By applying least-privilege principles, you reduce the risk of accidental or malicious data modification, while still allowing the agent to read the data it needs. You’ll need SQL Server Management Studio (SSMS) or another query tool, and permission to create database users and roles.

What You’ll Need

• A SQL Server instance (2016 or later is recommended).
• SQL Server Management Studio (SSMS) or an equivalent tool with administrative rights on the target database.
• The list of tables the agent is allowed to read.
• The name of the SQL Agent job or the login/application account that will run the queries.

Step-by-Step

1. Identify the account used by the agent. For SQL Agent jobs, this is typically the job’s owner or a proxy account. For applications, it’s the SQL login or Windows account that connects to the database. You’ll need to know this account to apply permissions.
2. Create a dedicated database role (recommended). In SSMS, connect to the database, expand Security > Roles > Database Roles, right-click and select New Database Role. Name it something like ReadOnlyAgent. This role will hold the permissions.
3. Grant SELECT on the approved tables only. Use the following T-SQL, replacing ReadOnlyAgent with your role name and the table list with your actual tables:

   GRANT SELECT ON dbo.Customers TO ReadOnlyAgent;
   GRANT SELECT ON dbo.Orders TO ReadOnlyAgent;
   GRANT SELECT ON dbo.Products TO ReadOnlyAgent;

   Do not grant any other permissions (INSERT, UPDATE, DELETE, EXECUTE) on these tables.

4. Deny access to all other tables (optional but strong). To ensure the agent cannot read any table not explicitly granted, you can deny SELECT on the entire schema, then grant on the approved tables. This is safer because new tables added later won’t be automatically accessible. Run:

   DENY SELECT ON SCHEMA::dbo TO ReadOnlyAgent;
   -- then grant on approved tables as above

   Note: DENY overrides GRANT, so you must grant after denying.

5. Add the agent’s login/user to the role. If the agent uses a SQL login, create a user in the database for that login (if not already exists) and add it to the role:

   CREATE USER [AgentLogin] FOR LOGIN [AgentLogin];
   ALTER ROLE ReadOnlyAgent ADD MEMBER [AgentLogin];

   If the agent uses a Windows account, use CREATE USER [DOMAIN\Account] FOR LOGIN [DOMAIN\Account]; and then add to the role.

6. For SQL Agent jobs specifically, set the job owner or proxy to that account. In SSMS, go to SQL Server Agent > Jobs, right-click the job, select Properties, and under General set the Owner to the login you configured. Alternatively, use a proxy for the job step that runs under that account.
7. Test the permissions. Run a SELECT query as the agent’s account against an approved table (should work) and against a non-approved table (should fail with a permission error). Also try an UPDATE on an approved table (should fail). You can test by using EXECUTE AS USER = 'AgentLogin' in a separate query window, or by running the job step.
8. Monitor and review. Periodically check the database permissions to ensure no new grants have been added. Use the system view sys.database_permissions to audit.

Common Mistakes

• Granting permissions at the schema level. If you grant SELECT on the entire schema, the agent can read all tables, including future ones. Always grant on specific tables unless you combine with a DENY.
• Forgetting to deny EXECUTE. If the agent can execute stored procedures, it might bypass table-level restrictions. Make sure to deny EXECUTE on any procedures that modify data.
• Using the sa or db_owner account. These have full access. Always use a least-privilege account.
• Not testing with the actual agent account. Permissions might work for your admin login but not for the agent’s login due to different roles.

Tips

• Use a dedicated role. This makes it easy to add or remove users without changing individual permissions.
• Consider using a view instead of direct table access. Create a view that only exposes the columns you want, and grant SELECT on the view only. This gives you column-level control.
• For SQL Agent jobs, use a proxy. Proxies allow you to run job steps under a specific credential without changing the job owner. This is cleaner for maintenance.
• Document your approved table list. Keep a record so future admins know what was intended.
• Use schemas to organize tables. If all approved tables are in a dedicated schema (e.g., readonly), you can grant on the schema and deny on others, which is simpler.

Troubleshooting

• Agent still cannot read approved tables. Check if the login has a user in the database. If not, create it. Also verify the role membership: SELECT IS_ROLEMEMBER('ReadOnlyAgent').
• Agent can read non-approved tables. This usually means you didn’t DENY on the schema, or the agent is a member of a higher-privilege role (like db_datareader). Remove that role membership.
• Agent can modify data. Check if the agent has IMPERSONATION rights or can execute dynamic SQL with elevated permissions. Ensure no EXECUTE grants exist.
• Permission errors are confusing. Use EXECUTE AS USER = 'AgentLogin' and then query sys.fn_my_permissions(NULL, 'DATABASE') to see effective permissions.
• Changes don’t take effect. Sometimes connections cache permissions. Ask the agent to reconnect, or restart the SQL Agent service if necessary.

Frequently Asked Questions

Can I use this method for a SQL Agent job that uses a proxy?

Yes. The proxy account must have a login and a database user. Add that user to the read-only role. The job step will run with those permissions.

What if I want to allow the agent to read only certain columns?

Create a view that selects only those columns, then grant SELECT on the view instead of the table. The agent cannot access columns not in the view.

Will this prevent the agent from using dynamic SQL to bypass restrictions?

Dynamic SQL runs under the same permissions as the caller, so it cannot bypass table-level DENY or lack of SELECT. However, if the agent has EXECUTE on a stored procedure that uses dynamic SQL with ownership chaining, it might. Avoid granting EXECUTE on such procedures.

Does this work for Azure SQL Database?

Yes, the same principles apply. You can create a database role and grant SELECT on specific tables. The main difference is that you cannot use Windows logins unless they are Azure AD users.

How do I revoke access if the agent no longer needs it?

Remove the user from the role: ALTER ROLE ReadOnlyAgent DROP MEMBER [AgentLogin];. Optionally, drop the user and the role if no longer needed.

What if the agent needs to read tables from multiple databases?

You’ll need to create the role and grants in each database. Cross-database queries require the agent to have permissions in each database, and the server may need TRUSTWORTHY or module signing for cross-database ownership chaining, which is more complex. In that case, consider using a linked server with restricted permissions or a service account with limited access.

Related

• Tar Command in Linux: How It Works
• Fix Discord Overlay Not Showing in Games
• Fix Microsoft Basic Display Adapter in Windows