Creating a Read‑Only User in SQL Server and Granting Stored Procedure Access

In SQL Server, protecting database security often requires creating separate logins with limited permissions; this article demonstrates how to set up a read‑only user and optionally allow that user to view stored procedures.

Creating the read‑only login

1. Open Microsoft SQL Server Management Studio (SSMS) and connect with an administrator account (e.g., Windows Authentication on a local server). 2. Expand the Security node, right‑click Logins and choose New Login . 3. In the General page, enter a login name, select SQL Server Authentication , and disable the password policy if desired. 4. Switch to the User Mapping page, select the databases the login should access, and check the db_datareader role (both in the database role membership and default schema). 5. Click OK – the read‑only login is now created and can only perform read operations on the mapped databases.

Granting permission to view stored procedures

If the read‑only user also needs to view stored procedures, use an administrator login (e.g., sa ) to modify the user’s permissions:

1. Right‑click the target login under Security → Logins and select Properties . 2. Go to Search under Security Objects and locate the server name. 3. In the permissions list, enable View any definition and save the changes. 4. Restart the SQL Server service (MSSQLSERVER) and ensure the SQL Agent is started.

Reference of server roles

Common fixed server roles include: • bulkadmin – can run BULK INSERT . • dbcreator – can create, alter, drop, and restore any database. • diskadmin – manages disk files. • processadmin – can terminate processes. • securityadmin – manages logins and permissions. • serveradmin – configures server‑wide options. • setupadmin – manages linked servers and certain system stored procedures. • sysadmin – has unrestricted access to all server functions.

Reference of database roles

Typical database roles include: • db_owner – full control of the database. • db_accessadmin – can add or remove users. • db_datareader – read‑only access to all tables. • db_datawriter – can insert, update, delete data. • db_ddladmin – can run DDL statements. • db_securityadmin – manages database‑level security. • db_backoperator – can back up the database. • db_denydatareader – denies read access. • db_denydatawriter – denies write access.

By following these steps, you can create a read‑only account in SQL Server, assign appropriate roles, and optionally grant stored‑procedure visibility, ensuring controlled access to your databases.