SQL Server Synonyms Without Over‑Granting Permissions

The Problem

A Power BI dataset was connecting to a database we’ll call DB01.

On the surface, the service account acme\svc_account had correct read access. But the dataset regularly failed with errors such as:

The server principal "acme\svc_account" is not able to access the database "DB02" under the current security context.

Why is Power BI touching DB02 when it’s supposed to query DB01?

The answer was SQL Server synonyms.

DB01 contained synonyms that pointed to objects in DB02. SQL Server enforces permissions on the underlying target, not the synonym. Since acme\svc_account had no rights on DB02, all queries failed.

The Wrong Fix

One tempting solution would be to grant db_datareader on DB02.

That would make the error disappear, but it would also give the service account access to every single table and view in DB02 — far more than needed, and a security risk.

The Safe Fix

Instead, we only want to grant rights to the specific objects in DB02 that are referenced by synonyms in DB01.

Steps:

• Identify all synonyms in DB01 and where they point.

• Generate precise GRANT statements for those objects in DB02.

• Apply those grants to acme\svc_account.

This limits access to the minimum set of tables/views that Power BI actually needs.

Step 1: find synonyms and their targets:

Step 2: generate GRANT statements automatically

The trick here is that sys.synonyms.base_object_name stores the target as a string, including square brackets.
We strip those out and use PARSENAME to extract DB, schema and object names.

output:

USE DB02; GRANT SELECT ON [dbo].[AddressFormat] TO [acme\svc_account];

You can review this list and then run it in DB02. The service account only gets exactly what it needs.

Step 3: show why this matters (Exposure Comparison)

Let’s compare narrow synonym‑based grants with broad db_datareader.

Example results:

In this example, giving the service account db_datareader would have exposed 985 objects.

The synonym‑based approach required access to only 12. That’s 98.8% less exposure.

Lessons Learned:

• Synonyms hide dependencies — always check if they cross database boundaries.

• SQL Server enforces rights on the target object, not the synonym.

• Never fix synonym permission problems with db_datareader.

• Instead, grant rights only on the objects your reporting user or service account needs.

Result: Power BI works, the service account stays least‑privileged, and your security posture remains strong.