April 10, 2012 at 6:42 pm
Hi All,
How can I give permissions to login to start SQL Agent job? I have a login which have no special permissions it has only public role and mapped with MSDB database. I followed below things but could not get the desired result.
1. Created a login with the name of ABC
2. Mapped the ABC login with MSDB database
3. Open the properties of ABC which is residing in MSDB
4. Ticked "Target Server Role" under Database Role Membership
5. Clicked on securables, added both stored procedure(sp_start_job and sp_stop_job) and ticked "EXECUTE" option and hit OK button
6. Created credentials and proxy accounts
7. In Proxy account creation I ticked Operating System(CMDExec) in subsystem and in Principal tab I added login ABC
8. Selected Job's properties selected steps tab and clicked on EDIT, in Type option I selected Operating System(CMDExec) and in Run As I selected the proxy account which I crated in step 6
9. I hit OK button, Now I logged in server with ABC and opened SQL Server management studio and executed :
USE msdb ;
GO
EXEC sp_start_job 'Backyp' ;
GO
but this query ended with the following output :
Msg 229, Level 14, State 5, Procedure sp_start_job, Line 1
The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'
I tried a lot to find out some clue of this errors but could not succeed please help me out as I have to revert to my customer as soon as possible.
Thanks in advance!!
April 10, 2012 at 10:10 pm
beejug1983 (4/10/2012)
Hi All,How can I give permissions to login to start SQL Agent job? I have a login which have no special permissions it has only public role and mapped with MSDB database. I followed below things but could not get the desired result.
1. Created a login with the name of ABC
2. Mapped the ABC login with MSDB database
3. Open the properties of ABC which is residing in MSDB
4. Ticked "Target Server Role" under Database Role Membership
5. Clicked on securables, added both stored procedure(sp_start_job and sp_stop_job) and ticked "EXECUTE" option and hit OK button
6. Created credentials and proxy accounts
7. In Proxy account creation I ticked Operating System(CMDExec) in subsystem and in Principal tab I added login ABC
8. Selected Job's properties selected steps tab and clicked on EDIT, in Type option I selected Operating System(CMDExec) and in Run As I selected the proxy account which I crated in step 6
9. I hit OK button, Now I logged in server with ABC and opened SQL Server management studio and executed :
USE msdb ;
GO
EXEC sp_start_job 'Backyp' ;
GO
but this query ended with the following output :
Msg 229, Level 14, State 5, Procedure sp_start_job, Line 1
The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'
I tried a lot to find out some clue of this errors but could not succeed please help me out as I have to revert to my customer as soon as possible.
Thanks in advance!!
Find out the permissions by below query and check if all required permission are there.
login with the use abc and execute this.
use msdb
SELECT * FROM fn_my_permissions('sp_start_job', 'OBJECT')
April 11, 2012 at 5:38 am
When I logged on with ABC and executed below script I could not find any result.
SELECT * FROM fn_my_permissions('sp_start_job', 'domain\ABC')
Even if I log on with other user who has sysadmin privilege and run the above script then also I don't find any output.
April 11, 2012 at 8:15 am
beejug1983 (4/11/2012)
When I logged on with ABC and executed below script I could not find any result.SELECT * FROM fn_my_permissions('sp_start_job', 'domain\ABC')
Even if I log on with other user who has sysadmin privilege and run the above script then also I don't find any output.
you'll need to grant exec permission to the sp_start_job stored proc using
use [msdb]
GO
GRANT EXECUTE ON [dbo].[sp_start_job] TO [TargetServersRole]
GO
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2012 at 5:37 pm
The issue still remaining same after all hits.
April 12, 2012 at 2:31 pm
For the new account ABC did you make it a member of the SQLAgent group?
Typically (in Windows) you go into "Control Panel\System & Security\Administrative Tools\Computer Management\Local Users & Groups", click on the user and make it a member of the group. By default the group is called SQLServerSQLAgentUser$<yourServerName><yourInstanceName> and is created automatically during installation.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply