Re: Rights to execute jobs?

The rules still apply when executing sp_start_job.

-Sue

On Thu, 16 Aug 2007 18:17:43 -0700, Brian Laws
<BrianLaws@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Interesting method. Thanks. I can put that in a stored procedure to make life
easier for people.

Does anyone know if the rules surrounding stored procedure permissions apply
to calling sp_start_job as well? If a user has permissions to run a stored
proc, than anything inside it can be done regardless of whether the user was
granted that access. Does this apply to a proc calling sp_start_job so that I
can have it kick off a job not owned by that user?

Thanks for your help!

Brian

"Russell Fields" wrote:

Brian,

One way to let users in a DEV environment be able to start jobs is to
trigger them through alerts. But I do not believe you can control who can
raise the alert, so it could be anyone on the server.

You can add an alert something like this:

EXEC msdb.dbo.sp_add_alert @name=N'Start My #1 Job',
@message_id=50101,
@severity=0,
@enabled=1,
@database_name=N'FavoriteDB',
@job_name=N'My #1 Job'

Of course, you need to define the messages (e.g. 50101) in sysmessages and
so forth, but the user only has to do a RAISERROR with the proper message
number, and the alert will start the job. You can put that in a procedure,
or whatever works best for you.

If this is good enough for you, it leaves your job definitions in
development and production just alike. Only, in production you would
probably not define the alerts.

RLF

"Brian Laws" <BrianLaws@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E24E82B3-531F-4FA7-B91B-69783DD7EE7B@xxxxxxxxxxxxxxxx
Yes, SQL2000. In SQL 2005 there are the new roles in the MSDB database
which
will grant rights, but I need something similar in 2000. Since we're using
Windows Authentication, we can't just have a shared SQL login. I could
create
a new account in common, but then there's no accountability. Plus, the
user
would then have to be the owner of the job, which breaks away from
standards
and makes development different from production.

"Chris Wood" wrote:

Brian,

Are you running SQL2000 or SQL2005? It looks like 2000 because you
mentioned
EM. We, just moving up to SQL2005, have a similar problem and have
created
an SQL login to run jobs. A Windows group cannot own a job and so, as you
say, cannot create jobs with other owners unless it is a sysadmin. If
running with a created SQL Login seems okay to you I can detail the
permissions that we are giving.

Chris

"Brian Laws" <BrianLaws@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AA330618-DB8C-48E9-A43B-BCD1C9053EF1@xxxxxxxxxxxxxxxx
Hello!

We're trying to lock down our environments (dev, qa, uat). One
requirement
is that a group needs to not be an admin but still be able to execute
any
job. I've looked and looked, but I haven't yet been able to find a good
resource for job execution permissions. I know that a job owner can
execute a
job, but our standard is to make all jobs owned by SA. So, can somebody
outline what permissions are required in order for a non-sysadmin to
run a
job not owned by themselves? Is that possible? It occurs to me that I
may
be
able to do this by making a stored proc to execute a passed-in job
name.
Since procs execute anything inside it (only permissions needed are to
the
proc itself), this may work. I'd like to avoid this, though, and let
them
run
through EM.

Thanks for the help!







.

Relevant Pages

  • Re: Rights to execute jobs?
    ... users can get admin type jobs going. ... raise the alert, so it could be anyone on the server. ... is that a group needs to not be an admin but still be able to execute ... resource for job execution permissions. ...
    (microsoft.public.sqlserver.security)
  • Solaris 10 autofs directory permissions - Solution
    ... the fact that my map file has 755 permissions not 644. ... If the execute permission is set, it becomes an executable map which is ... map is expected to return the content of an automounter map ...
    (SunManagers)
  • Re: Rights to execute jobs?
    ... One way to let users in a DEV environment be able to start jobs is to ... raise the alert, so it could be anyone on the server. ... is that a group needs to not be an admin but still be able to execute ... resource for job execution permissions. ...
    (microsoft.public.sqlserver.security)
  • Re: Rights to execute jobs?
    ... Does anyone know if the rules surrounding stored procedure permissions apply ... raise the alert, so it could be anyone on the server. ... is that a group needs to not be an admin but still be able to execute ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie
    ... I will now tell you some basic commands. ... Easy file has rights and a owner. ... file and who may execute it. ... The last three characters are about the permissions of user ...
    (alt.linux)