Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to configure the SQL Server Job Agent using the following scripts

USE [msdb]
GO
CREATE USER [mydbuser] FOR LOGIN [mydbuser]
GO
EXEC sp_addrolemember N'db_datareader', N'mydbuser'
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'mydbuser'
GO
EXEC sp_addrolemember N'SQLAgentReaderRole', N'mydbuser'
GO
EXEC sp_addrolemember N'SQLAgentUserRole', N'mydbuser'
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'xp_cmdshell',1
go
reconfigure with override
go
sp_configure 'show advanced options',0
go
reconfigure with override

use master
go
GRANT EXECUTE on xp_cmdshell to db_job
go

use master
go
EXEC sp_xp_cmdshell_proxy_account 'mydomain\db_job', 'password-1'
go

CREATE CREDENTIAL [Job3] WITH IDENTITY = N'mydomain\mydomainuser',
    SECRET = N'mydomain\mydomainuser'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'LW_JobRunner', @credential_name=N'Job',
    @enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'LW_JobRunner', @subsystem_id=3
GO
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'LW_JobRunner',
    @login_name=N'mydbuser'
GO

/* Setting up job Category */

DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL',
    @name=N'MyJob'
go

USE MASTER
GO

EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
    N'DEV',            -- agent_domain_name
    N'Administrator',  -- agent_username
    N'password'        -- agent_password 

But the last step on executing the sp xp_sqlagent_proxy_account, says "Could not find the stored proccedure xp_sqlagent_proxy_account.

I'm sure I'm executing it on the master db with the sa credentials...

And because of this on adding a job I get an error like the

"Coud not find the server 'myIpAddress' in sys.servers. Verify that the correct server name was specified .

If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers."

share|improve this question
1  
The error message about sys.servers seems unrelated to me. Do you have a linked server named 'MyIpAddress'? If not, it seems the code in the job requires one. –  Aaron Bertrand Jan 15 '13 at 4:27
    
I actually don't have any linked servers –  user824910 Jan 15 '13 at 4:29
    
Well you have code apparently that thinks that you do. –  Aaron Bertrand Jan 15 '13 at 12:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.