March 27, 2008 at 7:45 am
You'r right!!
after doing some googling i got to know
When Mapping the MyUser to MSDB Database there we need to check following things
>db_ddladmin
>db_dtsadmin
>db_dtsoperator
and all SQLAgent roles.
Then this user will have rights to own/create/updated the job. and for this we need to have Proxy Account.
and things will work.
Note:adding Target serverrole along with above mentioned role then it doent work, so i omitted.
Appriciate for all your Help..
March 27, 2008 at 7:49 am
Now I am abl to create/update/execute the JOb thru above MyUser. But even if the Job executes successfully it dosnt do the task(defined in SSIS Pack) in my DB.
I mean though now I am ablt to execute the Job successfully but no DB Operation is Happening given in .DTSX file.Not Sure whether user should be given some other rights too???
May 2, 2008 at 5:03 am
Hi Vijay,
iam getting the some problem with running the scheduled jobs.
can you tell me the steps, how to create the jon in sql server agent jobs.
thanks
sathish
September 3, 2008 at 9:43 am
I had a similar situation where i wanted a group to only run/stop jobs, but not modify, etc...i created the following role, which is essentially the SQLAgentOperatorRole minus certain permissions:
USE [msdb]
GO
/****** Object: DatabaseRole [SQLAgentLimitedOperatorRole] Script Date: 06/24/2008 10:53:39 ******/
CREATE ROLE [SQLAgentLimitedOperatorRole] AUTHORIZATION [dbo]
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'SQLAgentLimitedOperatorRole'
GO
DENY EXECUTE ON sp_add_job to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_add_jobschedule to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_add_jobserver to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_add_jobstep to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_add_schedule to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_addtask to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_attach_schedule to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_delete_job to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_delete_jobschedule to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_delete_jobserver to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_delete_jobstep to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_delete_jobsteplog to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_delete_schedule to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_detach_schedule to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_droptask to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_maintplan_subplans_by_job to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_update_job to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_update_jobschedule to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_update_jobstep to [SQLAgentLimitedOperatorRole]
DENY EXECUTE ON sp_purge_jobhistory to [SQLAgentLimitedOperatorRole]
GO
Hope this helps...
November 27, 2008 at 3:51 am
EdVassie,
He might have the same problem like me - where we are not even allowed to download documents from the internet.
December 16, 2008 at 11:42 am
A good article today
How do I assign permissions to users to see SQL Agent Jobs?
May 28, 2009 at 6:20 pm
i believe granting the user with "SQLAgentReaderRole" in msdb should solve the issue
August 3, 2011 at 9:54 am
To add my 2 cents, I was also getting the following error even though the credentials and proxy were setup -
“Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.”
--when using SQL Server 2005 SP4 Std. & a SQLOper account setup, non-sa, as a proxy.
and the cause for my error message was the lack of Stored Procedure execution rights (since the SP was recreated with a new name). This particular SP also uses two linked servers, one to Oracle and one to SQL 2000.
If it helps anybody,
Lori
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply