When you create a job when your are connected from an other domain SQL Server cannot validate your NT authentication and a error as
|Cannot insert the value NULL into column ‘owner_sid’, table ‘msdb.dbo.sysjobs’; column does not allow nulls. INSERT fails.|
is shown. You can correct this by setting the job owner by hand with te following statement.
set owner_sid=suser_sid(‘Active Directory Groupname’,0)
where name = ‘jobname’
If you want to use delegation of control you might want to assign an AD group as owner of a group so a group of users can start or monitor that job. With the SQL Server Management Studio you will get an error because the group isn’t validating at creation.
|Msg 515, Level 16, State 2, Procedure sp_update_job, Line 275
Cannot insert the value NULL into column ‘owner_sid’, table ‘msdb.dbo.sysjobs’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
What you have to do is add the AD group as a login and give the group SQLAgentReadeRole and SQLAgentUserRole
CREATE LOGIN [domain/group] FROM WINDOWS
EXEC sp_addrolemember ‘SQLAgentReaderRole’,’domain/group’
EXEC sp_addrolemember ‘SQLAgentUserRole’,’domain/group’