processadmin role

I'm trying to allow my developers the ability to modify/execute their jobs and dts packages in production...without giving away the security farm so to speak.

Is the processadmin role a possibility?

BOL and the net only seems to say this role allows user to "manage process"...duh.

Your thoughts and advice would be great appreciated.The processadmin server role conveys the ability to kill a process (SPID) in SQL Server. Can't say as I would be comfy with a lot of people with that ability, myself.

In order to create/delete jobs, they will need access to the msdb database (by default all users do), and permissions on the following stored procedures, which also default to public:

sp_add_job
sp_add_jobschedule
sp_add_jobserver
sp_add_jobstep
sp_delete_job
sp_delete_jobschedule
sp_delete_jobserver
sp_delete_jobstep
sp_start_job
sp_stop_job
sp_update_job
sp_update_jobschedule
sp_update_jobstep|||If I give them this kind of access in msdb, won't it give them job and dts access to all databases?|||Jobs and DTS packages are stored only in the msdb database, so yes. That's just the way the system is set up. If their user ids can access all databases, you would have had that, anyway. I am not sure if a user can try to specify a different user to run a job.

processadmin role

I need a mamber of my team to monitor some jobs set up by a DBA using SQL Job
I dont want to give him sysadmin prviledge
He should be able to see the job history and re-run the job if it fail
Should i give him processadmin rol
what does processadmin role exactly d
ThanksHi,
Process admin Role
--
Members of Processadmin role can only kill a process. Although the
description for this role says that members can manage
the processes running in SQL Server, the only management option they have is
to kill a process.
By giving the process admin job the users cant execute or see the status of
SQL Jobs.
To view the history of job, DBA can assign the execute prev on
"sp_help_jobhistory" in MSDB database to the user.
To view the job status a normal user can execute the procedure "sp_help_job"
(When the user is not a member of the sysadmin group, sp_help_job will
impersonate the SQL Server Agent proxy account,
which is specified using xp_sqlagent_proxy_account. If the proxy account is
not available, sp_help_job will fail.)
Execute a Job:
sp_start_job
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role
can start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:9F863661-784C-45D7-AD32-AE86C88E5493@.microsoft.com...
> I need a mamber of my team to monitor some jobs set up by a DBA using SQL
Jobs
> I dont want to give him sysadmin prviledges
> He should be able to see the job history and re-run the job if it fails
> Should i give him processadmin role
> what does processadmin role exactly do
> Thanks

processadmin role

I need a mamber of my team to monitor some jobs set up by a DBA using SQL Jobs
I dont want to give him sysadmin prviledges
He should be able to see the job history and re-run the job if it fails
Should i give him processadmin role
what does processadmin role exactly do
Thanks
Hi,
Process admin Role
Members of Processadmin role can only kill a process. Although the
description for this role says that members can manage
the processes running in SQL Server, the only management option they have is
to kill a process.
By giving the process admin job the users cant execute or see the status of
SQL Jobs.
To view the history of job, DBA can assign the execute prev on
"sp_help_jobhistory" in MSDB database to the user.
To view the job status a normal user can execute the procedure "sp_help_job"
(When the user is not a member of the sysadmin group, sp_help_job will
impersonate the SQL Server Agent proxy account,
which is specified using xp_sqlagent_proxy_account. If the proxy account is
not available, sp_help_job will fail.)
Execute a Job:
sp_start_job
Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role
can start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:9F863661-784C-45D7-AD32-AE86C88E5493@.microsoft.com...
> I need a mamber of my team to monitor some jobs set up by a DBA using SQL
Jobs
> I dont want to give him sysadmin prviledges
> He should be able to see the job history and re-run the job if it fails
> Should i give him processadmin role
> what does processadmin role exactly do
> Thanks

processadmin cannot kill

We are trying to enforce some security on our SQL server,
and I am trying to move several people away from logging
on to enterprise manager with the sa user and password. I
am hoping to use Windows Authentication, but I am having
problems with a user not being able to kill processes. I
have given the user access to all of the databases and
added them to the processadmin role. They still cannot
kill processes through enterprise manager. The SQL server
itself has not been topped & restarted, but that shouldn't
be nescisary, right (?).Hi Jason,
Could you give me an explanation of what process the user would need
to kill?
Are you referring to NT Processes or SQL spids ?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi,
It is not required to stop and start sql server after giving a role. Can you
try execution the below in query analyzer to create a SQL Server login and
assign processadmin role.
sp_addlogin <Login_name>
go
sp_addsrvrolemember <login_name>,processadmin
After executing the script, login to Querry analyser using the new sql
server login.
Execute sp_who to identify the process id and execute KILL <SPID> to kill a
process.
Once you are ok with this, try killing the process from Enterprise manager
by registering using this new user.
Thanks
Hari
MCDBA
"Jason" <jeliason@.youth-guidance.org> wrote in message
news:1c7e101c4524e$e2872190$a001280a@.phx
.gbl...
> We are trying to enforce some security on our SQL server,
> and I am trying to move several people away from logging
> on to enterprise manager with the sa user and password. I
> am hoping to use Windows Authentication, but I am having
> problems with a user not being able to kill processes. I
> have given the user access to all of the databases and
> added them to the processadmin role. They still cannot
> kill processes through enterprise manager. The SQL server
> itself has not been topped & restarted, but that shouldn't
> be nescisary, right (?).|||They are SQL SPIDs as viewed under Locaks/Process IDs
under Management in Enterprise Manager. A custom
application we use sometime generates "blocking locks"
which need to be cleared by the CIS manager.
Thanks!
Jason

>--Original Message--
>Hi Jason,
> Could you give me an explanation of what process the
user would need
>to kill?
>Are you referring to NT Processes or SQL spids ?
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>