Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / August 2008

Tip: Looking for answers? Try searching our database.

Grant pemission to run a job (no more)

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ray_s - 29 Aug 2008 20:35 GMT
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
Russell Fields - 30 Aug 2008 16:36 GMT
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

> 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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.