SQL Agent Job error

Question

It may be really simple question, but at the moment I have a kind of mental loop and need your help to break it. SQL server 2016 is installed using a domain account. I didn't create an explicit login, there is only a standard generic NT SERVICE\MSSQL$XXX login and all SQL Agent Jobs like backup, rebuild indices and dbcc checkdb are running with no problems, but if I create an SQL job running a simple select on the user database like select * from [AdventureWorks2016].[HumanResources].[EmployeePayHistory] where 1= 2 I get an error

Executed as user: [active directory account of the SQL Service]. 
Failed to initialize sqlcmd library with error number -2147467259. 
[SQLSTATE 42000] (Error 22050).  The step failed.

I have to create a login for SQL service domain account to fix an error.

Any ideas what happens were? Is this a security feature of the SQL server?

UPD: after a drill-down I've found an answer about sqlcmd error: it caused by msdb.dbo.sp_send_dbmail with @query parameter if the query in @query fails. But I do not have an answer to my main question: why I can't run a query over the user database using an SQL server service account.

UPD2: UPD2: as I realized my description is not that understandable, sorry for that. One more try. An SQL server is installed to run using active directory accout A. That means, it I open services.msc I see that account as a login account of the service. This configuration was implemented via configuration file, not as an after-setup change. SQL Server logins doesn't contain account A, only generic NT SERVICE\MSSQL$XXX and NT SERVICE\SQLAgent$XXX which are a members of sysadmin role as usual. All SQL maintenance jobs (index maintenance, DBCC and backups) are configured using Ola's scripts and running without any problems also on user databases. If I try to create an SQL Server Job to query user database even if it's a simple query like select * from [AdventureWorks2016].[HumanResources].[EmployeePayHistory] where 1= 2 the query can't run. SQL server trace shows an error: "The server principal A is not able to access the database "AdventureWorks2016" under the current security context" If I create a login A and add it to the sysadm role the job runs, no problems, but it's absolutely clear, as a sysadm it should not have problems to query a database. The point I do not understand: all other SQL jobs are running using generic account in the background, so there is a kind of impersonification in SQL: accout A is using a generic login. As an additional information: if I create a Login for A I can't delete it because SQL server means, User is logged in, so SQl user uses it in the backupground to connect to the SQL and to impersonificate a generic login. Why it's not possible if I query a user database via SQL Agent Job?

DrillDown 2nd:

  USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 19.01.2022 11:21:44 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'test_query_userdb_2Steps', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [query separate]    Script Date: 19.01.2022 11:21:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'query separate', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'select * from dbo.Users where 1=2', 
        @database_name=N'StackOverflow', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [query]    Script Date: 19.01.2022 11:21:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'query param', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'EXEC msdb.dbo.sp_send_dbmail
@profile_name=''my profile'',
@recipients=''my email'',
@subject=''ALERT: XX'',
@query =''select * from dbo.users where 1 = 2''', 
        @database_name=N'StackOverflow', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

The same query runs as a 1st step of the job and crashes the job if it runs as a @query parameter of send_mail SP. There is no permission context change between steps.

Answer 1

Answering Your Questions

Any ideas what happens were?

Possibly....

Is this a security feature of the SQL server?

No, not quite.

What happens when you create a job, is the job is created in the context of the user creating the job. So if you log in to the SQL Server instance with your DOMAIN\USER account and create a job, then that job will be created with DOMAIN\USER as the job owner. When it executes it will do so in the context of the job owner. This means that if the job owner does not have the permission to access the database via a database user, then the job will fail. This is what you might be observing.

If you want the job to run, then you are going to have to assign that user with certain database permissions.

You can do this in the SQL Server Login - Properties. Navigate in your SQL Server instance down the tree and find the login you want to modify:

• INSTANCE (SQL Server xx.xxx.xxxx - ..) |
  • Security |
    • Logins

The SQL Server Login will look a bit like this:

Now navigate to the User Mapping section:

Here you select the Database you wish to be able to read from, which will automatically create the same DOMAIN\USER Database user for you. Assign a default schema if required (normally dbo) and then in the lower portion select the generic database role db_datareader.

Close all windows with OK.

Now you have a Database User that matches your SQL Server Login (no, they are not the same) and you should be able to select from your desired database.

Answering Another Of Your Unspoken Questions

Why do the maintenance jobs run?

The jobs you run for backups, etc, are normally executed in the context of the service accounts and they are normally set to have the SQL Server Role sysadmin.

A sysadmin (An account assigned to the SQL Server Role 'sysadmin`) can performn nearly anything on a SQL Server instance.

SQL Server Roles can be found in the SQL Server Login - Server Roles section of the SQL Server Login.

Summary

• SQL Server Logins are allowed to connect to the SQL Server instance.
• Database Users are allowed to perform certain actions against a database.
• SQL Server Logins are linked to Database Users (normally) to GRANT ... specific permissions.
• SQL Server Service Accounts are automatically created as sysadmin which doesn't require them to be assigned to specific Database Users. So they can access any database and perform any maintenance task. This is not recommended for normal Users and/or Applicaitons accessing databases.
• SQL Server Logins can be named differently than their corresponding Database User.
• When you create a SQL Server Agent Job, it is normally created in the context of the current User Account, be it a DOMAIN\USER or a SQL Server Login (e.g. SomeSQLUser). This account must have access to the database it will be interacting with.