In SQL 2000, change the job owner to sysadmin.
In SQL 2005, change the job security context.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<ryan_willow@.hotmail.com> wrote in message
news:1151015746.073933.305090@.r2g2000cwb.googlegroups.com...
>I have a stored procedure in a database on a server, and this stored
> procedure uses a linked server.
> The login I was given is only granted DBO permissions on this database.
> I am trying to set this up so that the above stored procedure runs once
> per month, so I naturally tried to set up a Job which would do this.
> I have discovered from another group that:
> "You cannot access a linked server from a TSQL jobstep if you aren't
> sysadmin."
> So: How can I automatically run this stored procedure once per month
> under these conditions?
> Thanks,
> Ryan
>I'm in SQL 2000.
I cannot change the job owner because my permissions do not allow this.
Apparently a dbo user named 'User' can only create a job with owner
'User'. I am trying to implement this without having to step outside
my dbo security level. Is this going to be possible, and if so how?
Thanks.
Arnie Rowland wrote:[vbcol=seagreen]
> In SQL 2000, change the job owner to sysadmin.
> In SQL 2005, change the job security context.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <ryan_willow@.hotmail.com> wrote in message
> news:1151015746.073933.305090@.r2g2000cwb.googlegroups.com...|||That will not be possible without coordination with the server
administrator. Since Jobs have the potential for significant performance
issues as well as security issues for the server, the security context for
Jobs is sysadmin. (in SQL2000).
And of course a SQL Server Administrator is probably not going to allow you
to have permission to create or change a job because of the potential that
it could execute any code or activity under the sysadmin security context.
Jobs are a 'Server' level activity -NOT a database level activity. Even if a
job is confined to a single database, it still operates as a server level
activity.
That changes with SQL 2005.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Ryan" <ryan_willow@.hotmail.com> wrote in message
news:1151017863.128715.208750@.g10g2000cwb.googlegroups.com...
> I'm in SQL 2000.
> I cannot change the job owner because my permissions do not allow this.
> Apparently a dbo user named 'User' can only create a job with owner
> 'User'. I am trying to implement this without having to step outside
> my dbo security level. Is this going to be possible, and if so how?
> Thanks.
> Arnie Rowland wrote:
>|||Very informative, thanks Arnie.
Arnie Rowland wrote:[vbcol=seagreen]
> That will not be possible without coordination with the server
> administrator. Since Jobs have the potential for significant performance
> issues as well as security issues for the server, the security context for
> Jobs is sysadmin. (in SQL2000).
> And of course a SQL Server Administrator is probably not going to allow yo
u
> to have permission to create or change a job because of the potential that
> it could execute any code or activity under the sysadmin security context.
> Jobs are a 'Server' level activity -NOT a database level activity. Even if
a
> job is confined to a single database, it still operates as a server level
> activity.
> That changes with SQL 2005.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Ryan" <ryan_willow@.hotmail.com> wrote in message
> news:1151017863.128715.208750@.g10g2000cwb.googlegroups.com...|||I have a stored procedure in a database on a server, and this stored
procedure uses a linked server.
The login I was given is only granted DBO permissions on this database.
I am trying to set this up so that the above stored procedure runs once
per month, so I naturally tried to set up a Job which would do this.
I have discovered from another group that:
"You cannot access a linked server from a TSQL jobstep if you aren't
sysadmin."
So: How can I automatically run this stored procedure once per month
under these conditions?
Thanks,
Ryan|||In SQL 2000, change the job owner to sysadmin.
In SQL 2005, change the job security context.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<ryan_willow@.hotmail.com> wrote in message
news:1151015746.073933.305090@.r2g2000cwb.googlegroups.com...
>I have a stored procedure in a database on a server, and this stored
> procedure uses a linked server.
> The login I was given is only granted DBO permissions on this database.
> I am trying to set this up so that the above stored procedure runs once
> per month, so I naturally tried to set up a Job which would do this.
> I have discovered from another group that:
> "You cannot access a linked server from a TSQL jobstep if you aren't
> sysadmin."
> So: How can I automatically run this stored procedure once per month
> under these conditions?
> Thanks,
> Ryan
>|||I'm in SQL 2000.
I cannot change the job owner because my permissions do not allow this.
Apparently a dbo user named 'User' can only create a job with owner
'User'. I am trying to implement this without having to step outside
my dbo security level. Is this going to be possible, and if so how?
Thanks.
Arnie Rowland wrote:[vbcol=seagreen]
> In SQL 2000, change the job owner to sysadmin.
> In SQL 2005, change the job security context.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <ryan_willow@.hotmail.com> wrote in message
> news:1151015746.073933.305090@.r2g2000cwb.googlegroups.com...|||That will not be possible without coordination with the server
administrator. Since Jobs have the potential for significant performance
issues as well as security issues for the server, the security context for
Jobs is sysadmin. (in SQL2000).
And of course a SQL Server Administrator is probably not going to allow you
to have permission to create or change a job because of the potential that
it could execute any code or activity under the sysadmin security context.
Jobs are a 'Server' level activity -NOT a database level activity. Even if a
job is confined to a single database, it still operates as a server level
activity.
That changes with SQL 2005.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Ryan" <ryan_willow@.hotmail.com> wrote in message
news:1151017863.128715.208750@.g10g2000cwb.googlegroups.com...
> I'm in SQL 2000.
> I cannot change the job owner because my permissions do not allow this.
> Apparently a dbo user named 'User' can only create a job with owner
> 'User'. I am trying to implement this without having to step outside
> my dbo security level. Is this going to be possible, and if so how?
> Thanks.
> Arnie Rowland wrote:
>|||Very informative, thanks Arnie.
Arnie Rowland wrote:[vbcol=seagreen]
> That will not be possible without coordination with the server
> administrator. Since Jobs have the potential for significant performance
> issues as well as security issues for the server, the security context for
> Jobs is sysadmin. (in SQL2000).
> And of course a SQL Server Administrator is probably not going to allow yo
u
> to have permission to create or change a job because of the potential that
> it could execute any code or activity under the sysadmin security context.
> Jobs are a 'Server' level activity -NOT a database level activity. Even if
a
> job is confined to a single database, it still operates as a server level
> activity.
> That changes with SQL 2005.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Ryan" <ryan_willow@.hotmail.com> wrote in message
> news:1151017863.128715.208750@.g10g2000cwb.googlegroups.com...
