Skip to main content
Navigation

SQL Server: Read-Only Login

Create a secure, minimal-privilege SQL Server login for FreshGuard.

This guide walks you through creating a dedicated read-only SQL Server login and user for FreshGuard. The login has the minimum privileges needed to run monitoring queries.

Create the Login and User

Connect to your SQL Server instance as sa or a user with ALTER ANY LOGIN and ALTER ANY USER privileges, then run:

-- Create a server-level login
CREATE LOGIN freshguard_reader
  WITH PASSWORD = 'your-strong-password-here';


-- Switch to the target database
USE Analytics;


-- Create a database user mapped to the login
CREATE USER freshguard_reader FOR LOGIN freshguard_reader;


-- Grant read-only access
ALTER ROLE db_datareader ADD MEMBER freshguard_reader;

Use a strong password

Generate a random password of at least 32 characters. SQL Server supports passwords up to 128 characters.

Multiple Databases

Repeat the user creation and role assignment for each database you want to monitor:

USE Staging;
CREATE USER freshguard_reader FOR LOGIN freshguard_reader;
ALTER ROLE db_datareader ADD MEMBER freshguard_reader;


USE Warehouse;
CREATE USER freshguard_reader FOR LOGIN freshguard_reader;
ALTER ROLE db_datareader ADD MEMBER freshguard_reader;

Schema-Level Access

If you want to restrict access to specific schemas instead of the entire database:

-- Instead of db_datareader, grant SELECT on specific schemas
GRANT SELECT ON SCHEMA::dbo TO freshguard_reader;
GRANT SELECT ON SCHEMA::analytics TO freshguard_reader;

Verify Permissions

Connect as the new login and confirm it can read but not write:

-- This should work
SELECT COUNT(*) FROM dbo.your_table;


-- This should fail with "INSERT permission denied"
INSERT INTO dbo.your_table (id) VALUES (0);

SQL Server on Linux

The same commands work on SQL Server running on Linux. Connect via sqlcmd or any SQL Server client and run the T-SQL above.

Tip

After creating the login, enter freshguard_reader and the password you set when connecting SQL Server in FreshGuard.