Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
matt_calhoon
Posting Yak Master
235 Posts |
 Posted - 2004-05-18 : 03:33:12
|
| Hi there,I have a stored procedure that fires off an sql agent job. However, I have found that I can only do this if I give the db user System Administration role. Is there any lower security permissions that I can set but still allow the user to run the stored proc which fires off the job?CREATE PROCEDURE p_START_EXPORT_JOBASEXEC msdb..sp_start_job @job_name ='DATA_EXPORT' |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-18 : 08:27:08
|
| GRANT EXEC ON sp_start_job TO userMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
matt_calhoon
Posting Yak Master
235 Posts |
Posted - 2004-05-26 : 03:42:16
|
| I have tried this (in enterprise manager > Database > User > Permissions) but it still does not work. It only works if I make the user a System Administrator.Any other thoughts? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-26 : 08:45:49
|
| The user has to own the job also. If you click on the job, you will see the job owner. You need to make the appropriate user the job owner. You could make this work by having your application operate under one user. That user would need to be the owner of the job. From BOL just as an fyi:Execute permissions default to the public role in the msdb database. A user who can execute this procedure and is a member of the sysadmin fixed role can start any job. A user who is not a member of the sysadmin role can use sp_start_job to start only the jobs he/she owns.When sp_start_job is invoked by a user who is a member of the sysadmin fixed server role, sp_start_job 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 fixed server role, sp_start_job will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, sp_start_job will fail. This is only true for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and sp_start_job is always executed under the security context of the Windows 9.x user who started SQL Server.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
stored procedure run an sql agent job