Invisible Jobs and DTS owners

[btgreen]

Hello,

Is there anyway to make all jobs visible to everyone. We can currently only see our own jobs and this causes issues with trying to do other people's work when they are absent.
I think this has something to do with the permissions on the SQL Server. We all authenicate with Windows NT logins.

Thanks for the help.

 

[MaffewW]

check out authentication information under DTS packages in Books On Line

 

[btgreen]

Thank you for your reply. The information in this above reference seems cryptic and does not provide an apparent answer.

 

[tlbroadbent]

There is no way to allow users to update jobs owned by other users except by adding the users to the system adminstrators role which is usually not an option. The SA could change the job owner as needed. This could be done easily with script.

Here's a script that generates the SQL statements to change the owner for all Jobs owned by "bob" to "sue." The resulting script could be saved and modified later to change the Jobs' owner back to "bob."

Use msdb
set nocount on
go

If Exists
(Select *
From msdb.dbo.sysjobs j
Left Join master.dbo.syslogins l
On j.owner_sid=l.sid
Where l.name='bob')
Begin
Select [Use msdb]='Exec sp_update_job @job_id = '''+convert(varchar(85),j.job_id)+''', @owner_login_name = ''sue'''
From msdb.dbo.sysjobs j
Left Join master.dbo.syslogins l
On j.owner_sid=l.sid
Where l.name='bob'
End
Else
Print 'bob owns no jobs on this server.'

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[btgreen]

Thank you very much. SA was an option for the time being so we are back in action.