Sql-server – Running queries/jobs via SMSS – SQL service and SQL agent service accounts (SQL Server)

service-accountssql serversql-server-agentt-sql

If a command, for example the below command, is executed from SQL Server Management Studio while I am logged in the session with a windows authentication login, I understood that the access to the path 'C:\SQL2019\BookStore.bak'is done via the sql service account (set in the SQL Server Configuration Manager) and not via my login. However, if an INSERT statement is done, is it done via my login in that case?

BACKUP DATABASE [BookStore] TO  DISK = N'C:\SQL2019\BookStore.bak' 
WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'BookStore-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Also, I have an agent job set and in the job history it states "Executed as user: sqlagent1" which is the sql agent service account that I have set in the SQL Server Configuration Manager. The run as in the job step properties has been left empty. However, there is no login created for this account under the Logins in SQL Server. From where does this account get permission?

Best Answer

You want to differentiate between doing something inside SQL Server (like the INSERT you mention) and have SQL Server or Agent do something in the OS.

Doing something inside SQL Server (INSERT, for example): You login using some login (CREATE LOGIN), and then this login has a user created in the database (CREATE USER). The privileges are based on that user, with the usual things such as role membership etc. This is a big topic, but I have a feeling that you are actually after the next section. Just want to make sure we differentiate the two.

Have SQL Server or Agent do something at the OS level: Now you want to differentiate between SQL Server and Agent.

An example when SQL Server does anything at the OS level is BACKUP. This is always done using the SQL Server service account. (Two exceptions are xp_cmdshell where a server-wide proxy is used and bulk loading of data in some cases when an impersonation of the end-users windows account can be used.)

Then you have when Agent does something at the OS level. Yes, we're talking about all job-steps except T-SQL. For a sysadmin, by default the Agent's service account is user. For non-sysadmin, you have created and Agent Proxy to be used (basically point to a windows account and allow the job owner to use this). A sysadmin can also use an Agent Proxy if desired.

Finally you have when Agent does something inside SQL Server. T-SQL job-steps, in other words.

  • For sysadmin, Agent does it as "itself" using the Agent login, which is a Windows authentication. This login is btw always a sysadmin.
  • For other job owner (non-sysadmins), then agent will automatically
    use EXECUTE AS LOGIN = 'job_owner_name' WITH NO REVERT. I.e., the
    stuff is done as the job owner's login context without we having to
    do anything.

Here are a couple of (old) blog posts I've written on this topic: http://sqlblog.karaszi.com/sql-server-agent-jobs-and-user-contexts/ and http://sqlblog.karaszi.com/xp_cmdshell-and-permissions/.

Related Question