They only need to be able to create a new schedule for an existing job. Essentially, I want them to be able to call a stored proc at the time of their choosing.
I read your article -- very informative. I've added it to my bookmarks, right beside your article on dynamic SQL, to which I've referred many a time over the years.
So what I've done -- which works -- is:
1) Create the sproc in the user DB WITH EXECUTE AS SELF.
By my understanding, this should be me. But...
2) Grant execute to the DB group to which the the users belong.
Now, when I call the sproc using EXECUTE AS <one of the users>, and do a SELECT SUSER_SNAME(), it returns the database owner -- which is not me?? In the same query window, but outside the stored procedure, SELECT SUSER_SNAME() returns my account.
I also tried creating the sproc WITH EXECUTE AS 'Domain\database owner account', but I get an error, as that login does not explicitly exist - it's a member of an AD group that has sa privs. It's also the account under which the SQL Server service operates. I would have expected this to work, however.
Finally, I tried WITH EXECUTE AS 'Domain\my account', and this also works. I'm a member of the same AD group as the 'Domain\database owner account'. So I wonder now:
1) how come SELF <> me
2) why it doesn't work when I specify the 'Domain\database owner account'?