Read-only SQL login for SyteLine reporting

Short answer

Give reporting tools their own login that can read and nothing else. Preferably use a Windows (Active Directory) group, so access follows people joining and leaving. Map it to a user in your reporting database with SELECT on the reporting views, and to a user in the SyteLine database with SELECT on only the tables those views read, or db_datareader if you accept broader read access. Never give a reporting tool db_owner, sysadmin, or the account SyteLine itself runs as. Then prove it can't write, by trying.

Why it matters

Reporting access is the most common way SyteLine databases end up over-exposed:

A spreadsheet is given the SyteLine application's own SQL account "because it worked."
A Power BI gateway connects as a DBA account.
A contractor's login, set up for one report, still has db_owner two years later.

Any of those can change data, not just read it. A mistyped query or a tool that "helpfully" writes back can corrupt orders, balances or inventory. Writes made directly with SQL also skip SyteLine's business logic and event handlers completely.

	Windows group (recommended)	SQL Server login
Who's in it	Managed in Active Directory	Shared username and password
Leavers	Removed from the group, access gone	Password must be changed for everyone
Audit	Shows the real person	Shows only the shared name
When to use	People and tools on your domain	Tools that can't use Windows auth, such as some cloud services

If you must use a SQL login, give each tool its own one, store the password in a password manager, and change it when people with access leave.

Recommended setup: views plus minimum table access

This pairs with Connecting Power BI and Excel to SyteLine safely, which covers the reporting views themselves and where to put them. Example script, with placeholder names to replace (DOMAIN\SyteLine-Reporting, SyteLineReporting, SyteLine_App):

-- 1. Server login for the AD group
CREATE LOGIN [DOMAIN\SyteLine-Reporting] FROM WINDOWS;
GO

-- 2. Reporting database: read the views only
USE SyteLineReporting;
CREATE USER [DOMAIN\SyteLine-Reporting] FOR LOGIN [DOMAIN\SyteLine-Reporting];
GRANT SELECT ON SCHEMA::rpt TO [DOMAIN\SyteLine-Reporting];
GO

-- 3. SyteLine database: read only the tables the views use
USE SyteLine_App;
CREATE USER [DOMAIN\SyteLine-Reporting] FOR LOGIN [DOMAIN\SyteLine-Reporting];
GRANT SELECT ON dbo.co_mst       TO [DOMAIN\SyteLine-Reporting];
GRANT SELECT ON dbo.coitem_mst   TO [DOMAIN\SyteLine-Reporting];
GRANT SELECT ON dbo.custaddr_mst TO [DOMAIN\SyteLine-Reporting];
-- ...one line per table your views read
GO

If your views live inside the SyteLine database instead

If you put the rpt schema inside the SyteLine database rather than a separate one, skip step 3: within one database, views owned by dbo over dbo tables work through ownership chaining, so GRANT SELECT ON SCHEMA::rpt is enough. The trade-offs between the two placements are in Connecting Power BI and Excel to SyteLine safely.

Why step 3 is needed (the cross-database catch)

When a view in one database reads tables in another, SQL Server checks the user's permissions on those underlying tables too. Without step 3, the views return permission errors.

You'll find advice to switch on cross-database ownership chaining to avoid this. Don't. It's off by default for good security reasons. Granting SELECT on the specific tables is cleaner and easy to audit.

Simpler, broader option

If listing tables is too much maintenance, you can replace the GRANT SELECT lines in step 3 with:

ALTER ROLE db_datareader ADD MEMBER [DOMAIN\SyteLine-Reporting];

db_datareader can read every table in the SyteLine database, including payroll, bank details and user data, if your system holds them. Decide that consciously, and write the decision down.

What never to grant

db_owner, db_datawriter or sysadmin
The SQL account SyteLine or TaskMan runs as
EXECUTE on SyteLine stored procedures. Many of them change data.
Access to the report server database, unless someone genuinely needs the SSRS execution log

Prove it's read-only

Test it as someone who has only this access, on a test copy, never production. A Windows group can't be impersonated with EXECUTE AS, so either sign in to SQL Server Management Studio as a user who is only in that group, or create a temporary test SQL login with exactly the same grants. Then run:

-- run while signed in with ONLY the reporting access, on a TEST copy
SELECT TOP (5) * FROM SyteLineReporting.rpt.OpenOrderLines;      -- should work

UPDATE SyteLine_App.dbo.co_mst SET co_num = co_num WHERE 1 = 0;  -- should fail: permission denied

The UPDATE is written to change nothing even if it ran (WHERE 1 = 0). You're only checking that SQL Server refuses it. Delete any temporary test login afterwards.

To list what the group has been granted in a database, run this as an administrator:

-- what the reporting principal has been granted in this database
SELECT  pr.name AS principal, pe.permission_name, pe.state_desc,
        OBJECT_NAME(pe.major_id) AS object_name
FROM    sys.database_permissions AS pe
JOIN    sys.database_principals  AS pr ON pr.principal_id = pe.grantee_principal_id
WHERE   pr.name = 'DOMAIN\SyteLine-Reporting';

Look after it

Review the group membership in your yearly SyteLine security audit.
Add table grants when you add views, and remove them when views are retired.
Re-check after upgrades, in case database users or permissions were reset.
One login per purpose (Power BI gateway, report writers, integrations) makes it obvious who's using what.

FAQ

How do I give read-only SQL access to SyteLine?

Create a login, preferably for an Active Directory group, and grant it SELECT only: on your reporting views, and on just the SyteLine tables those views read, or db_datareader if you accept broader read access.

Is db_datareader safe for SyteLine reporting?

It can't write, but it can read every table in the database. Grant SELECT on specific tables if you want tighter control.

Why do my reporting views give permission errors?

Views reading another database need the user to have SELECT on the underlying tables in that database too.

Should Power BI use the SyteLine application's SQL account?

No. Give it its own read-only login, so it can't change data and its access can be removed on its own.