February 18, 2010 at 10:44 am
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.
February 18, 2010 at 11:15 am
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
February 18, 2010 at 11:44 am
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
February 18, 2010 at 11:51 am
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.
February 18, 2010 at 11:53 am
Seems there is no proper way to do this ...
February 18, 2010 at 12:22 pm
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
February 18, 2010 at 8:16 pm
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.
February 19, 2010 at 6:31 am
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
February 19, 2010 at 7:30 am
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
February 19, 2010 at 9:20 am
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
February 23, 2010 at 5:11 am
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