October 29, 2007 at 2:16 pm
Hello,
I've got a vendor that wants to be able to connect to a database (SQL 2000) and add some jobs. This server has multiple databases and I'm hesitant to give him access outside his product's db. What permissions do I need to give him to be able to set up and execute jobs, but restrict them to only his database?
Thanks,
Mike Lamar
October 30, 2007 at 3:25 am
Dear
you have to create another account like Vendor user
and make DBO of the particular database that you wana share with
the vendor.
Regards
Syed muhammad naveed
database Administrator
October 30, 2007 at 9:43 am
Mike,
You'll have to grant the vendor some permissions in msdb. I'd create a role, add the user to it, and grant the following permissions to the role:
sp_add_job sp_add_jobstep sp_add_jobschedule
sp_update_job sp_update_jobstep sp_update_jobschedule
sp_help_job sp_help_jobstep sp_help_jobschedule
sp_delete_job sp_delete_jobstep sp_delete_jobschedule
sp_help_jobhistory
sp_start_job
sp_stop_job
Unless you make the vendor a user in other databases, he won't be able to access anything in them.
Greg
Greg
October 30, 2007 at 9:57 am
Greg's advice is technically correct, but why do it?
You are right to be concerned about 3rd party access to your live systems. Get them to script the job creation, so you can deploy it, or tell them to sling their hook.
October 30, 2007 at 10:00 am
Joseph Mulhall (10/30/2007)
Greg's advice is technically correct, but why do it?You are right to be concerned about 3rd party access to your live systems. Get them to script the job creation, so you can deploy it, or tell them to sling their hook.
Thanks, that was my thinking as well. I had him send me the script and I ran it for him.
Mike
April 22, 2009 at 2:38 pm
are you asking for a Microsoft Product called MOM by chance? I am looking for the same reason as I don't want to give blanket SysAdmin access to MOM since it has the ability to do Tasks against the database. Did giving permissions to these SPs work?
April 23, 2009 at 1:20 am
Greg thanks. You've answered something I've been needing to do for a while as my fellow developers need to manage jobs but not be sa and so far we've let them have sa on the dev box.
April 23, 2009 at 10:30 am
sorry, dup
April 23, 2009 at 10:30 am
these are the execute grants I ran. I can now execute sp_help_job on a sql 2000 db but it doesn't return any data. Do we need to address any SELECT access to any MSDB tables?
-------
use [msdb]
GO
GRANT EXECUTE ON [dbo].[sp_add_job] TO [User1]
GRANT EXECUTE ON [dbo].[sp_add_jobstep] TO [User1]
GRANT EXECUTE ON [dbo].[sp_add_jobschedule] TO [User1]
GRANT EXECUTE ON [dbo].[sp_update_job] TO [User1]
GRANT EXECUTE ON [dbo].[sp_update_jobstep] TO [User1]
GRANT EXECUTE ON [dbo].[sp_update_jobschedule] TO [User1]
GRANT EXECUTE ON [dbo].[sp_help_job] TO [User1]
GRANT EXECUTE ON [dbo].[sp_help_jobstep] TO [User1]
GRANT EXECUTE ON [dbo].[sp_help_jobschedule] TO [User1]
GRANT EXECUTE ON [dbo].[sp_delete_job] TO [User1]
GRANT EXECUTE ON [dbo].[sp_delete_jobstep] TO [User1]
GRANT EXECUTE ON [dbo].[sp_delete_jobschedule] TO [User1]
GRANT EXECUTE ON [dbo].[sp_help_jobhistory] TO [User1]
GRANT EXECUTE ON [dbo].[sp_start_job] TO [User1]
GRANT EXECUTE ON [dbo].[sp_stop_job] TO [User1]
April 23, 2009 at 10:30 am
these are the execute grants I ran. I can now execute sp_help_job on a sql 2000 db but it doesn't return any data. Do we need to address any SELECT access to any MSDB tables?
-------
use [msdb]
GO
GRANT EXECUTE ON [dbo].[sp_add_job] TO [User1]
GRANT EXECUTE ON [dbo].[sp_add_jobstep] TO [User1]
GRANT EXECUTE ON [dbo].[sp_add_jobschedule] TO [User1]
GRANT EXECUTE ON [dbo].[sp_update_job] TO [User1]
GRANT EXECUTE ON [dbo].[sp_update_jobstep] TO [User1]
GRANT EXECUTE ON [dbo].[sp_update_jobschedule] TO [User1]
GRANT EXECUTE ON [dbo].[sp_help_job] TO [User1]
GRANT EXECUTE ON [dbo].[sp_help_jobstep] TO [User1]
GRANT EXECUTE ON [dbo].[sp_help_jobschedule] TO [User1]
GRANT EXECUTE ON [dbo].[sp_delete_job] TO [User1]
GRANT EXECUTE ON [dbo].[sp_delete_jobstep] TO [User1]
GRANT EXECUTE ON [dbo].[sp_delete_jobschedule] TO [User1]
GRANT EXECUTE ON [dbo].[sp_help_jobhistory] TO [User1]
GRANT EXECUTE ON [dbo].[sp_start_job] TO [User1]
GRANT EXECUTE ON [dbo].[sp_stop_job] TO [User1]
February 7, 2012 at 8:47 am
Also check any groups got deny access on these SP's that user got then user wont be able to execute these inspite of having execute access.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply