After a security hardening project, the ETL service account still connects to the database successfully, but every INSERT into the staging table now fails with a permission denied error. The job needs to insert and read rows in that table only. What should the DBA do?
Select an answer to reveal the explanation.
Short Explanation
A successful login with a denied INSERT tells you exactly where the problem is: you're in the building, you just don't have the key to that room. So you hand over that one key, INSERT and SELECT on the staging table, not the master key to the whole database.
Full Explanation
Connection-related issues in data acquisition separate into authentication, proving who the account is, and authorization, what the account may do. Here authentication works, because the connection succeeds, but authorization fails on a specific operation, which points to missing object permissions, most likely removed during hardening. Applying least privilege, the DBA grants only the permissions the job actually requires, INSERT and SELECT on the staging table, ideally through a role assigned to the service account so the permissions are documented and auditable. Adding the account to the database owner role would restore the load but grant far more than needed, including the ability to alter or drop objects and change security, which defeats the hardening and widens the impact if the account is compromised. Running the job under a DBA's personal account mixes human and service identities, breaks accountability in the audit trail and exposes a highly privileged credential to an automated process. Resetting the password targets authentication, which is already working; an expired or wrong password would prevent the connection itself, not fail on a single INSERT. Exam caveat: document service account permissions so that future hardening can be checked against what integration jobs need. Operational check: after the grant, rerun the job, confirm it inserts and reads rows, and confirm it still cannot perform operations outside its scope, such as dropping the table.