A spreadsheet can turn a careful database into a busy shared service. Connecting Excel to SQL Server deserves the same access review as any application. Give the workbook only the data it needs, then control how and when it refreshes.
Begin Connecting Excel to SQL Server With a Workbook View
Create a view that exposes only approved columns and rows. Give fields stable names and choose types Excel can interpret consistently. If the workbook needs a monthly total, consider returning a monthly total rather than every transaction. The view becomes a contract that can be reviewed independently from the workbook. Avoid SELECT star, since a new sensitive column should not appear in a refresh by accident.
I ask who will open the workbook and where copies will go before I approve connecting Excel to SQL Server. A spreadsheet can leave a controlled share by email in seconds. Does the audience need raw customer detail, or only a grouped result? That decision belongs before a connection string is saved. The data boundary should survive even if the workbook is copied.
Use a Dedicated Read-Only Identity
Create a login or identity for this reporting workload, with access restricted to the intended database and views. Grant SELECT on those views through a narrow database role. Do not make the identity db_owner to avoid a permissions conversation. A read-only user with broad table access can still reveal more data than the workbook needs, so view scope matters as much as write protection.
If Windows authentication or a managed identity fits the environment, prefer it over a shared SQL password. Record who owns the identity and how it is revoked. I test the connection with the workbook’s actual identity, not my administrator account. An administrator test proves only that administrators can read data. That discovery will not surprise anyone.
Inspect the Permission Surface
The catalog query below lists explicit permissions in the current database. It is a review tool, not a complete effective-permission calculator. Role membership, ownership chaining, and higher server privileges also affect access. Run it in the reporting database, then inspect the rows for the workbook role and identity. A clean list of view grants is easier to explain than a scattered set of table permissions.
Use a separate test session signed in as the reporting identity to verify that the intended view works and an unrelated base table does not. Record both outcomes. When a view uses objects with different owners or crosses databases, check the resulting permission behavior explicitly. Do not assume every view hides the tables behind it.
SELECT
USER_NAME(dp.grantee_principal_id) AS Grantee,
dp.state_desc,
dp.permission_name,
dp.class_desc,
OBJECT_SCHEMA_NAME(dp.major_id) AS SchemaName,
OBJECT_NAME(dp.major_id) AS ObjectName
FROM sys.database_permissions AS dp
WHERE dp.class_desc = 'OBJECT_OR_COLUMN'
ORDER BY Grantee, SchemaName, ObjectName;Treat Refresh After Connecting Excel to SQL Server as Workload
Power Query and connection refreshes can run expensive SQL on the server. A workbook opened by several people can multiply the same scan. Decide whether refresh is manual, scheduled, or driven by a central published extract. Put an owner on that schedule. If the query aggregates a large table, inspect its plan and indexing with a representative filter before offering it to every desktop.
The next query shows current user requests and their elapsed time while refresh is active. Run it only with the permissions needed to see the relevant sessions. Use it to find the program name and command, then investigate the specific query text and plan through approved monitoring. Do not call a workbook harmless because its output has only a few cells.
SELECT
r.session_id,
s.program_name,
s.host_name,
r.command,
r.status,
r.total_elapsed_time
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
WHERE s.is_user_process = 1
ORDER BY r.total_elapsed_time DESC;
Keep Credentials Out When Connecting Excel to SQL Server
A workbook can save connection settings and, depending on the method, credentials. Do not save a reusable SQL password inside a workbook that people can copy. Review Power Query connection options and the deployment method before distribution. Use the organization’s credential store or identity flow, and let each authorized user authenticate under policy. A protected worksheet is not a password vault.
Check a copy of the final workbook with an ordinary user account. Close and reopen it, inspect its data sources, and confirm the refresh path asks for or obtains credentials as intended. Also decide whether cached result data inside the workbook is allowed. Removing the password does not remove the last refresh’s data from the file.
Plan for Parameters and Filters
If users choose dates or departments, bind those choices as parameters in the connector rather than building SQL by concatenating cell text. Put bounds on date ranges where the view or stored procedure allows it. A single wrong selection should not scan years of operational detail. Keep the selected parameters visible in the workbook so readers know what a result represents.
I inspect refresh queries when a workbook first becomes popular. A small pilot can hide the load that appears after broad rollout. Use Query Store or approved workload monitoring to compare query duration and reads across refreshes. The numbers must come from your server, not a guess in the workbook design.
Decide How Stale Is Acceptable
Connecting Excel to SQL Server directly is not the only way to serve a spreadsheet. An approved extract or reporting database can isolate refresh pressure from a transactional instance. A readable secondary is useful only if the workload and licensing support it, and its data freshness meets the business need. State the expected lag or refresh schedule plainly so users do not treat yesterday’s values as live.
If the workbook must be live, document the peak usage window and test concurrency. If it can be delayed, a controlled refresh can be simpler and safer. The choice should follow the decision the spreadsheet supports. Finance close and a quick operational dashboard have different freshness needs.
Review the Connection After Changes
When the underlying schema changes, test the view and workbook together. A renamed column can break refresh even when the table is healthy. Review permissions when staff or data classifications change. Keep the workbook owner, view definition, identity, and refresh schedule in the same record.
A good handoff includes how to revoke access, where to find refresh errors, and who approves new fields. That keeps a successful one-off workbook from becoming an unowned production integration. The spreadsheet remains useful because its database contract is small enough to inspect.
Related reading on this blog: How to Import a SQL Server Table Inside Excel Sheet? Interview Question of the Week #273 and Export Data From SSMS Query to Excel.
A workbook connection is not a shortcut around database controls, it is another client that needs a clear contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
