Windows group as job owner

  • DBA's

    I have an issue with job ownership. As per my understanding, Jobs cant be owned by windows group, but in a scenario I need windows group to be owner of job, or something which can simulate this.

    We have a SQL server job, which will be called by an application. This application uses windows authentication to connect sql server, a number of users which are part of a windows group can access this application. Now, we want this SQL job to be executed by this application, without granting too much priviledge to the group. SQLAgentUserRole of msdb is good enough for executing jobs, provided login is owner of the job, but we cant have windows group as owner of jobs.

    Please see if I am missing something, if not, is there any workaround.

  • I think you'll need to grant the ability to run jobs to the group.

    Is it possible the product of the job can be produced in another manner? If, for example, a proc or DLL could do it, you could have more control over that.

    SQL Agent jobs are usually used for scheduling internal tasks in my experience. I've never seen one called by a user application. Is that more common than I think?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Lately, I have seen more and more applications create Agent jobs for "grooming" the database. Mostly re-index jobs, and "clean-up" jobs, they are typically created by the application, and invoked by the application.

    That being the case, most of these applications do not authenticate to SQL Server with Windows Authentication. Typically this is done with a service account.

    Karl Lambert
    SQL Server Database Administration
    Business Intelligence Development

  • Thanks for your reply. I will test the possiblity of creating a proc, lets see if it work with no extra permission.

    Jobs like SSIS execution etc. can be used on demand ... its very common in our environment (there are several applications running, all having their own set of procedures and business logic), for them, we use a separate sql login to run such jobs, but this is not the case with this application which is concerned here.

  • Seems there is no proper way to do this ...

  • See my reply in this thread: http://www.sqlservercentral.com/Forums/Topic863044-146-1.aspx#bm863413. In my case, the objective was to trigger execution of an SSIS package from an application.

    Greg

  • I would create a sproc that run's as a user that has the privileges to start the relevant job. This is really easy to do and, given that the logic on what is permitted is controlled by the sproc, you really don't have too many security issues. The users in your group don't need any SQL Agent privileges.

  • SSIS packages don't have to be run as jobs. They can be run from the command line using dtexec.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • KISS !!

    Just use an alert to launch the job !

    Have a look at my little article "help to tighten use of cmdshell or sp_start_job"

    http://www.sqlservercentral.com/scripts/Miscellaneous/31032/

    This way, you get all nice stats info by sqlagent job logging, you can disable the alert responce, ...

    If you directly provide means to launch the job or package, you nolonger have the control to shud it off during maintenance.

    And your users only need login access to your server and database if you only want sqlagent to respond to the alert if being raised from a certain database !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • GSquared (2/19/2010)


    SSIS packages don't have to be run as jobs. They can be run from the command line using dtexec.

    Yeah, I know. What I was trying to illustrate for the OP was one way to start a job from an application. In my case, the job happened to run a package.

    Greg

  • If you use a SSIS package, you need to consider how the package will be initiated. Wherever the application is running (e.g. on a workstation for a Win32 type application or on web server), you will need to install the SSIS binaries. Whilst not that difficulat, it is something else to remember.

    You also need keep the workstation or server up to date with service packs and relevant SQL updates (for SSIS).

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply