//
you're reading...
Database, IT-Center, SQL Server

SQL Server 2005 Jobs Error : Cannot insert the value NULL into column ‘owner_sid’, table ‘msdb.dbo.sysjobs’; column does not allow nulls.

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

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

April 2012
M T W T F S S
« Mar   May »
 1
2345678
9101112131415
16171819202122
23242526272829
30  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: