Forum Discussion
DB change of account
Our database jobs, such as full database backups, transaction log backups, system database backups, Reorg job , Update stats and few DB related jobs and task scheduler jobs for housekeeping the trace files , are owned by one of our SQL DB account, which also has DB and SQL server admin privileges .
Without disrupting the above-mentioned job schedules, we would like to remove the DB admin & SQL admin privileges for that account . I.e, let this account be used only schedule SQL agents job purpose . Could you please help me obtain information from support how to convert that account without impact of DB operations ?
To remove DB and SQL admin privileges without affecting your jobs, you need to:
Check Job Ownership: Ensure that the SQL jobs are owned by the account with admin rights, but also check that the necessary permissions are granted for executing jobs.
Grant Specific Permissions: Instead of full admin rights, assign the SQL Agent Operator or SQL Agent User roles to the account. These roles allow the account to run jobs without needing full admin privileges.
Test with a Non-Admin Account: Test the account with limited permissions on a test environment to confirm the jobs still run properly.
You should reach out to support to confirm the specific steps for your setup.
Why? Several of the mentioned jobs needs evelvated permissions for the service account to run them, e.g. BACKUP (Transact-SQL) - SQL Server | Microsoft Learn => Permissions
