executing job on another server

EXEC server7.msdb.sp_start_job @.job_name = 'mailresults'
I am on server3 and have to run a from here on server7 does anyone know the
correct syntax I should use for this task
thanks
for any help
SammyHTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Sammy" <Sammy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:ED235F23-3BD6-4B10-BEEB-3920F97681D8@.microsoft.com...
> EXEC server7.msdb.sp_start_job @.job_name = 'mailresults'
> I am on server3 and have to run a from here on server7 does anyone know
> the
> correct syntax I should use for this task
> thanks
> for any help
> Sammy|||You even have to connect to the remote server, or write a on the remote
server SP to execute the statement you mentioned.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Sammy" <Sammy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:ED235F23-3BD6-4B10-BEEB-3920F97681D8@.microsoft.com...
> EXEC server7.msdb.sp_start_job @.job_name = 'mailresults'
> I am on server3 and have to run a from here on server7 does anyone know
> the
> correct syntax I should use for this task
> thanks
> for any help
> Sammy|||See sp_add_jobserver in BOL.
AMB
"Sammy" wrote:

> EXEC server7.msdb.sp_start_job @.job_name = 'mailresults'
> I am on server3 and have to run a from here on server7 does anyone know th
e
> correct syntax I should use for this task
> thanks
> for any help
> Sammy|||EXEC server7.msdb.dbo.sp_start_job @.job_name = 'mailresults'
Once the linked server is setup.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ubwsBLjWFHA.1040@.TK2MSFTNGP10.phx.gbl...
> You even have to connect to the remote server, or write a on the remote
> server SP to execute the statement you mentioned.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Sammy" <Sammy@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:ED235F23-3BD6-4B10-BEEB-3920F97681D8@.microsoft.com...
>

execute xp_cmdshell and other SA storedproc

Hi all,

I have to execute stored procedures containing
xp_cmdshell and certain system storedprocedures in msdb and master
with a user who is not SA.
(i.e iam able to execute stored procedures when i log as sa,
but any other user cannot run them)

Pls tell how to do this, it is quite urgent.Books online reviews:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft Windows NT 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.

Follow the corresponding links on BOOKS ONLINE about this topic.

Execute permission denied on object xp_SQLagent_notify

SQL Server 2000, SP4. I have this login MyLogin which has access in both msd
b
and master. The account is public in master and db_owner in msdb. I have the
same settings on 10 servers. I attempt to create a job, steps and schedule.
When it comes to msdb.dbo.sp_add_jobserver I got the following messages:
Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
'master', owner 'dbo'.
Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
EXECUTE permission denied on object 'xp_sqlagent_notify', database 'master',
owner 'dbo'.
The puzzling part here is that I got the errors on 2 servers out of the 10
above mentioned. On the other 8 the job is created successfully and there ar
e
no explicit rights granted or denied on these particular XPs.
Question: What are the minimum requirements to execute the above 2 XP? They
are not documented by Microsoft or it seems I cannot find much on them. Ther
e
is always the possibility to explicitly GRANT access to them for MyLogin, bu
t
the question remains: why is it working on 8 servers and not working on the
other 2. Must be some other setting somewhere!
Any answer will be higly appreciated.Gabriela,
On my server the xp_sqlagent* stored procedures are granted execute to
public. Check on your two problem servers to see whether that is true for
you.
If the agent XPs are not enabled, you can do so by:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
RLF
"Gabriela Nanau" <Gabriela Nanau@.discussions.microsoft.com> wrote in message
news:36F16EC4-7BFF-421B-B397-2BEB931FF1ED@.microsoft.com...
> SQL Server 2000, SP4. I have this login MyLogin which has access in both
> msdb
> and master. The account is public in master and db_owner in msdb. I have
> the
> same settings on 10 servers. I attempt to create a job, steps and
> schedule.
> When it comes to msdb.dbo.sp_add_jobserver I got the following messages:
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
> EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
> 'master', owner 'dbo'.
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
> EXECUTE permission denied on object 'xp_sqlagent_notify', database
> 'master',
> owner 'dbo'.
> The puzzling part here is that I got the errors on 2 servers out of the 10
> above mentioned. On the other 8 the job is created successfully and there
> are
> no explicit rights granted or denied on these particular XPs.
> Question: What are the minimum requirements to execute the above 2 XP?
> They
> are not documented by Microsoft or it seems I cannot find much on them.
> There
> is always the possibility to explicitly GRANT access to them for MyLogin,
> but
> the question remains: why is it working on 8 servers and not working on
> the
> other 2. Must be some other setting somewhere!
> Any answer will be higly appreciated.|||As I said in my first post, I don't have GRANT or DENY for the xp_SQLAGENT%
SPs (not even for public) on any of the servers (the ones that work or the
ones that don't).
As for the sp_configure 'Agent SPs', I am on SQL Server 2000, not such
option there!
So thanks, but it doesn't help.
Gabriela Nanau
MCDBA
"Gabriela Nanau" wrote:

> SQL Server 2000, SP4. I have this login MyLogin which has access in both m
sdb
> and master. The account is public in master and db_owner in msdb. I have t
he
> same settings on 10 servers. I attempt to create a job, steps and schedule
.
> When it comes to msdb.dbo.sp_add_jobserver I got the following messages:
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
> EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
> 'master', owner 'dbo'.
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
> EXECUTE permission denied on object 'xp_sqlagent_notify', database 'master
',
> owner 'dbo'.
> The puzzling part here is that I got the errors on 2 servers out of the 10
> above mentioned. On the other 8 the job is created successfully and there
are
> no explicit rights granted or denied on these particular XPs.
> Question: What are the minimum requirements to execute the above 2 XP? The
y
> are not documented by Microsoft or it seems I cannot find much on them. Th
ere
> is always the possibility to explicitly GRANT access to them for MyLogin,
but
> the question remains: why is it working on 8 servers and not working on th
e
> other 2. Must be some other setting somewhere!
> Any answer will be higly appreciated.|||Gabriela Nanau (Gabriela Nanau@.discussions.microsoft.com) writes:
> SQL Server 2000, SP4. I have this login MyLogin which has access in both
> msdb and master. The account is public in master and db_owner in msdb. I
> have the same settings on 10 servers. I attempt to create a job, steps
> and schedule. When it comes to msdb.dbo.sp_add_jobserver I got the
> following messages:
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
> EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
> 'master', owner 'dbo'.
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
> EXECUTE permission denied on object 'xp_sqlagent_notify', database
> 'master', owner 'dbo'.
> The puzzling part here is that I got the errors on 2 servers out of the
> 10 above mentioned. On the other 8 the job is created successfully and
> there are no explicit rights granted or denied on these particular XPs.
> Question: What are the minimum requirements to execute the above 2 XP?
> They are not documented by Microsoft or it seems I cannot find much on
> them. There is always the possibility to explicitly GRANT access to them
> for MyLogin, but the question remains: why is it working on 8 servers
> and not working on the other 2. Must be some other setting somewhere!
I would guess this is a owner-chaining issue. Since there are no perms
granted to revoked to these SP:s, MyLogin should not be able to execute
these procedures directly on any server.
However, when MyLogin runs sp_add_jobserver, permission is granted
through ownership chaining, if the two procedures have the same owner.
To have this:
1) The two databases must have the same owner.
2) Cross-DB chaining must be enabled for the databases.
According to Books Online is DB chaining always on for master and msdb,
so my guess is that on the two servers you have problem, master and msdb
have different owners.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||To add to Erland's response, you can fix msdb ownership can database options
using the script below.
USE msdb
EXEC sp_changedbowner 'sa'
EXEC sp_dboption 'msdb', 'db chaining', true
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabriela Nanau" <Gabriela Nanau@.discussions.microsoft.com> wrote in message
news:36F16EC4-7BFF-421B-B397-2BEB931FF1ED@.microsoft.com...
> SQL Server 2000, SP4. I have this login MyLogin which has access in both
> msdb
> and master. The account is public in master and db_owner in msdb. I have
> the
> same settings on 10 servers. I attempt to create a job, steps and
> schedule.
> When it comes to msdb.dbo.sp_add_jobserver I got the following messages:
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
> EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
> 'master', owner 'dbo'.
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
> EXECUTE permission denied on object 'xp_sqlagent_notify', database
> 'master',
> owner 'dbo'.
> The puzzling part here is that I got the errors on 2 servers out of the 10
> above mentioned. On the other 8 the job is created successfully and there
> are
> no explicit rights granted or denied on these particular XPs.
> Question: What are the minimum requirements to execute the above 2 XP?
> They
> are not documented by Microsoft or it seems I cannot find much on them.
> There
> is always the possibility to explicitly GRANT access to them for MyLogin,
> but
> the question remains: why is it working on 8 servers and not working on
> the
> other 2. Must be some other setting somewhere!
> Any answer will be higly appreciated.|||Thanks a lot. The different ownership was indeed, the problem! I didn't even
think to check the owners for these 2 databases! It seemed so obvious that i
t
must be sa! That was a rookie's error, I'm sort of embarrassed! Now that I
think about, the msdb was brought from a different machine at some point and
it was probably then when the owner changed.
Once again thanks!
--
Gabriela Nanau
MCDBA
"Dan Guzman" wrote:

> To add to Erland's response, you can fix msdb ownership can database optio
ns
> using the script below.
> USE msdb
> EXEC sp_changedbowner 'sa'
> EXEC sp_dboption 'msdb', 'db chaining', true
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gabriela Nanau" <Gabriela Nanau@.discussions.microsoft.com> wrote in messa
ge
> news:36F16EC4-7BFF-421B-B397-2BEB931FF1ED@.microsoft.com...
>

EXECUTE permission denied on object ''xp_sqlagent_enum_jobs''

SQL Server 2005 SP2, v9.00.3042

Last week, I set up a SQL Server login and assigned it to the MSDB role of SQLAgentOperatorRole. A couple of jobs were created and this login was assigned as being the owner of those jobs.

The login was able to successfully edit and execute the jobs. Per the documentation, only these 2 jobs would show up in the jobs list for the login to view.

Now, when the login attempts to expand the jobs list, the following error appears:

EXECUTE permission denied on object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'

I'm not excited about granting explicit execute permissions to extended stored procedures . . . . but will if I have to.

I think the senior DBA changed something that hosed this up.

What should I be looking for?

Also, I should point out that the login was able to successfully able to execute the jobs on our "upgrade test' instance of 2005. Then we created a production instance and restored the MSDB backup of the upgrade instance to the production instance.

I'm thinking the problem is somehow related to this . . . .

EXECUTE permission denied on object ''xp_sqlagent_enum_jobs''

SQL Server 2005 SP2, v9.00.3042

Last week, I set up a SQL Server login and assigned it to the MSDB role of SQLAgentOperatorRole. A couple of jobs were created and this login was assigned as being the owner of those jobs.

The login was able to successfully edit and execute the jobs. Per the documentation, only these 2 jobs would show up in the jobs list for the login to view.

Now, when the login attempts to expand the jobs list, the following error appears:

EXECUTE permission denied on object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'

I'm not excited about granting explicit execute permissions to extended stored procedures . . . . but will if I have to.

I think the senior DBA changed something that hosed this up.

What should I be looking for?

Also, I should point out that the login was able to successfully able to execute the jobs on our "upgrade test' instance of 2005. Then we created a production instance and restored the MSDB backup of the upgrade instance to the production instance.

I'm thinking the problem is somehow related to this . . . .

Execute msdb.dbo.sp_sqlagent_get_perf_counter

Hello:
Need some help. I do not recall setting up to run
msdb.dbo.sp_sqlagent_get_perf_counter
But it is running and taking up quite a bit of CPU time, about 60%, in the
server. Would it be ok if I kill it. Any benefit in running it?
I do not see it in the Jobs list, where is it set?
Thanks,
QIf you haven't defined any alerts, it is from the Demo
alerts installed by default. You can delete them if you
aren't using them.
The stored procedure checks performance conditions for
alerts. It won't run if you don't have any alerts defined.
If you do have alerts, it runs at a default of every 20
seconds which is in the registry under
HKLM\Software\Microsoft\Microsoft SQL
Server\YourInstance\SQLServerAgent.
Under SQLServerAgent, the value for
PerformanceSamplingInterval is set in seconds. Setting it to
0 will disable this entirely if you aren't using any such
alerts.
-Sue
On Wed, 14 Dec 2005 17:46:02 -0800, "Q"
<Q@.discussions.microsoft.com> wrote:
>Hello:
>Need some help. I do not recall setting up to run
>msdb.dbo.sp_sqlagent_get_perf_counter
>But it is running and taking up quite a bit of CPU time, about 60%, in the
>server. Would it be ok if I kill it. Any benefit in running it?
>I do not see it in the Jobs list, where is it set?
>Thanks,
>Q