Re: Regular users rying to run sp_start_job
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 14 Sep 2007 22:01:11 +0000 (UTC)
INTP56 (INTP56@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I have a situation where I would like "regular users", typically users
who just have db_reader and db_writer roles in a specific database to be
able to start a job via sp_start_job. I'm having trouble understanding
permissions.
As a test, I have a job called SendMail, which simply sends me an email
with the time the job executed.
...
I've tried many variations, but it seems every user fails to execute
unless I go to msdb and specifically make them a user, and exec as
CALLER, which is the default.
What is different about EXECUTE AS CALLER verses EXECUTE AS
'DomainName\martinrk1' when they are the same person? Is there a
reasonable way to call jobs this way, of should I just abandon this
approach alltogether?
When you do EXECUTE AS something else than caller, you are sandboxed
into the current database, unless the database has been set as trustworthy.
Which is a harmless thing to do on a server where you have control
over all databases. And which is not a thing to do at whim on a server
where different people manages different databases.
In any case, my preference is to use certificate signing instead. It's
a little more complicated, but EXECUTE AS has some nasty implications.
Rather than telling you the details here, I refer you to a longer
article on my web site that discusses both certificates and EXECUTE
AS in detail: http://www.sommarskog.se/grantperm.html.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Regular users rying to run sp_start_job
- From: INTP56
- Regular users rying to run sp_start_job
- Prev by Date: Re: "Account not associated with a trusted SQL Server connection"
- Next by Date: Re: Regular users rying to run sp_start_job
- Previous by thread: Regular users rying to run sp_start_job
- Next by thread: Re: Regular users rying to run sp_start_job
- Index(es):
Relevant Pages
|
