Hi,
on sql 2005, I want to grant a particular user (login) the permission to run
a single sql job and nothing else. Is this possible, and how to do that?

Thanks

Ray,

If that user is the owner of the job, then he can start it whenever he
wants. Of course, that probably does not satisfy your requirement, so
another approach is to use RAISERROR.

You can create a stored procedure to use RAISERROR to raise an alert. The
alert can be configured to start a job. You also have to create a message
number with logging.

USE msdb
EXEC msdb.dbo.sp_add_job @name = N'Make Something Special Happen', ...
USE master
EXEC sp_addmessage @msgnum=90001, @severity=10,
@msgtext=N'Starting -- Make Something Special Happen',
@with_log = 'True'
USE msdb
EXEC msdb.dbo.sp_add_alert
@name=N'Start Make Something Special Happen',
@message_id=99901, @severity=0,
@enabled=1, @delay_between_responses=0,
@include_event_description_in=1,
@database_name=N'DBNameWhereTheStoredProcExists',
@job_Name=N'Make Something Special Happen'

The stored procedure would then contain the following code:
RAISERROR 99901,10,1

The upside is that the job remains owned by the proper account and the user
needs no elevation of rights to start it. The downside of this is that
anyone who knows the alert error number can RAISERROR for it. So you don't
actually need a stored procedure, but that could be a way of limiting who
knows the error number. (Especially if you create it as an encrypted stored
procedure.)

RLF

"Ray_s" <NoOne@Hotmail.com> wrote in message
news:OmYEw5gCJHA.1228@TK2MSFTNGP02.phx.gbl...
> Hi,
> on sql 2005, I want to grant a particular user (login) the permission to
> run a single sql job and nothing else. Is this possible, and how to do
> that?
>
> Thanks
>
>