How to create a read-only user in Microsoft SQL Server

Before connecting your SQL Server database to any external tool, create a dedicated read-only user. This limits the blast radius if credentials are ever leaked and prevents accidental writes from queries that go wrong.

SQL Server separates a server-level login from a database-level user. You create the login once, then map it to a user in the specific database you want to expose.

Connect as sa (or another sysadmin) against the master database:

CREATE LOGIN byesql_reader WITH PASSWORD = 'Choose-a-strong-Password1!';

SQL Server enforces its password policy by default (length, complexity). Pick a strong password.

Step 2: Create the database user and grant read access

Switch to the database you want ByeSQL to query:

USE your_database_name;
GO

CREATE USER byesql_reader FOR LOGIN byesql_reader;

-- Read every table and view in the database:
ALTER ROLE db_datareader ADD MEMBER byesql_reader;

db_datareader grants SELECT on all current and future tables and views in the database. The user cannot INSERT, UPDATE, DELETE, or run DDL.

Step 3: Connect ByeSQL

In the connection form use:

Host / Port: your SQL Server host and port (default 1433)
Database: your_database_name
Schema: dbo (or the schema your tables live in)
Username / Password: byesql_reader and its password

Limiting access to specific tables

If you need to hide certain tables, for example a payment_methods or user_credentials table, do not add the user to db_datareader. Grant SELECT per object instead:

USE your_database_name;
GO

CREATE USER byesql_reader FOR LOGIN byesql_reader;

GRANT SELECT ON dbo.orders    TO byesql_reader;
GRANT SELECT ON dbo.products  TO byesql_reader;
GRANT SELECT ON dbo.customers TO byesql_reader;

The user (and the schema ByeSQL introspects) will see only those three tables.

Verify the setup

Connect as the read-only user and test:

SELECT TOP 1 * FROM dbo.orders;              -- should succeed
INSERT INTO dbo.orders (quantity) VALUES (1); -- should fail: INSERT permission denied

Revoking access

USE your_database_name;
GO
DROP USER byesql_reader;
GO
USE master;
GO
DROP LOGIN byesql_reader;

A note on defense in depth

A read-only SQL Server user is your database-level safeguard. ByeSQL adds a second layer through SAFE mode: all write operations (INSERT, UPDATE, DELETE, DROP, TRUNCATE, ALTER, MERGE) are blocked at the application level, and every generated query is capped with TOP / OFFSET … FETCH before it reaches your database.

Both controls together mean that even if one layer has a gap, the other catches it. Use both.

Step 1: Create the login