Cannot insert the value NULL into column
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.
update msdb.dbo.sysjobs_view
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
|
USE [master]
GO
CREATE LOGIN [domain/group] FROM WINDOWS
GO
USE [msdb]
GO
EXEC sp_addrolemember 'SQLAgentReaderRole','domain/group'
GO
EXEC sp_addrolemember 'SQLAgentUserRole','domain/group'
GO
|
|
