How to create a read-only SQL Server login (step by step)
Sooner or later, something other than your main business system needs to read your SQL Server database: a reporting tool, an accountant's spreadsheet, a monitoring service, an AI assistant. The safest way to allow that is a login that can read but cannot change anything.
It is tempting to hand over the sa login, or the one the POS system itself uses, because it already works. Please don't. Those logins can change and delete data. A mistake in a report, or a tool that misbehaves, could damage the records your business runs on.
This guide creates a proper read-only login in a few minutes. It works on SQL Server 2012 and later, including the free Express edition that many POS systems use.
What you need
- SQL Server Management Studio (SSMS), the free tool from Microsoft, on the server or on a computer that can reach it.
- A login that is allowed to create logins, usually a Windows administrator account or
sa. You use it once, to set things up. - The name of the database your business system uses. In SSMS it is listed under Databases.
Step 1: Allow SQL Server logins (if needed)
SQL Server can accept Windows logins only, or Windows logins and SQL Server logins together. A separate read-only login needs the second option.
- In SSMS, right-click the server at the top of the tree and choose Properties.
- Open Security.
- Under Server authentication, choose SQL Server and Windows Authentication mode.
- Click OK. If you changed it, restart the SQL Server service (right-click the server → Restart). The change only takes effect after a restart.
If your POS system already logs in with a SQL Server login, this is already set and you can skip the step.
Step 2: Create the login and give it read access
Open a New Query window and run the following. Replace ShopDB with your database's name and choose your own long password.
-- 1. A login for the whole server
CREATE LOGIN cuedis_reader
WITH PASSWORD = 'Choose-A-Long-Password-1',
CHECK_POLICY = ON;
-- 2. A user for that login inside your business database
USE ShopDB;
CREATE USER cuedis_reader FOR LOGIN cuedis_reader;
-- 3. Allow it to read every table and view
ALTER ROLE db_datareader ADD MEMBER cuedis_reader;
-- 4. Optional: allow it to see how views and procedures are written
GRANT VIEW DEFINITION TO cuedis_reader;
What each part does:
CREATE LOGINcreates the account at server level. On its own it cannot see any database.CREATE USER ... FOR LOGINgives the login an identity inside one database only.db_datareaderis a built-in role that allowsSELECTon every table and view in that database, and nothing else. It cannot insert, update or delete.VIEW DEFINITIONlets the tool see the definitions of views and stored procedures without being able to run or change them. Useful for tools that want to understand your existing reports. Leave it out if you do not need it.
If the business data is spread over several databases, repeat steps 2 to 4 (from USE onwards) for each one.
Step 3: Check that it really is read-only
Connect in SSMS with the new login: choose SQL Server Authentication, type cuedis_reader and the password. Then try a harmless read and a write:
USE ShopDB;
SELECT TOP 5 * FROM dbo.Sales; -- should work
UPDATE dbo.Sales SET Amount = Amount; -- should fail
The UPDATE should be refused with a permission was denied error. If it is not, the login is a member of another role. Check under Security → Logins → cuedis_reader → Properties → User Mapping.
Step 4: Let other computers connect
If the tool runs on the same computer as SQL Server, you are done. If it runs on another computer on your network, three settings usually need attention:
- TCP/IP must be on. Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for , and enable TCP/IP. Then restart the SQL Server service.
- Named instances need the SQL Server Browser. If your server name looks like
PC-NAME\SQLEXPRESS, the instance probably uses a changing port. Either start the SQL Server Browser service, or set a fixed port under TCP/IP → IP Addresses → IPAll. - Windows Firewall must allow it. Allow incoming TCP on port 1433 (or your fixed port) and, if you use the Browser service, UDP on 1434. Limit the rule to your office network if you can.
Never open these ports to the internet. A reporting or monitoring tool belongs on your office network, not on a public address.
Optional: belt and braces
db_datareader on its own already prevents writing. If you want extra certainty, or other people may add roles to this user later, you can also deny writing explicitly:
USE ShopDB;
ALTER ROLE db_denydatawriter ADD MEMBER cuedis_reader;
db_denydatawriter blocks insert, update and delete on every table, even if write permission is granted some other way later.
On older servers
On SQL Server 2008 and 2008 R2, ALTER ROLE ... ADD MEMBER does not exist yet. Use the older procedure instead:
EXEC sp_addrolemember 'db_datareader', 'cuedis_reader';
Removing it later
If you stop using the tool, remove the user from each database and then the login:
USE ShopDB;
DROP USER cuedis_reader;
DROP LOGIN cuedis_reader;
Why this matters for any tool you connect
A read-only login is the simplest, strongest protection you can give your data. It does not depend on the tool behaving well, and it is easy to check. It is also why Cuedis checks the login it is given: if the login can change data, Cuedis refuses it and shows these steps. Whatever you connect to your database, give it a login like this one.