RE: exec sp_help_job user account rights

Hi,

found this limitations in BOL:
_____________________
Permissions
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
also create, delete, or update a job, job step, job category, job schedule,
job server, task, or job history information. A user who is not a member of
the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns.

When sp_help_job is invoked by a user who is a member of the sysadmin fixed
server role, sp_help_job will be executed under the security context in which
the SQL Server service is running. 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. This is true only for
Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and sp_help_job is always executed under the security context
of the Windows 9.x user who started SQL Server.

_______________



I'm not sure if I can make a workaround for this, or we need to redevelop
our monitoring tools to comply our new security policies.

If you have any tip, please heeeeeeeeeeeeeelp :)

Thank you,
Vladimir




"struchnjak" wrote:

Hi All,

Which access rights are required for one user to be able to get results from
the following:
exec msdb..sp_help_job @job_name = 'TEST',@job_aspect = N'job';

the same user can get the list of jobs from:
select * from sysjobs order by name

I can make it working only if I put the user into the sysadmin server role.
This is rather to much for a monitoring purpose.

Thank you,
Vladimir
.

Relevant Pages

  • Re: Run Jobs in Enterprise Manager
    ... Execute permissions default to the public role in the msdb database. ... A user who is not a member of the sysadmin role can use ... sp_start_job will impersonate the SQL Server ... > I have several DTS packages in Enterprise Manager that are run as jobs in SQL Server Agent. ...
    (microsoft.public.sqlserver.dts)
  • Re: grant access to extended properties
    ... Do you know anything about this "EXECUTE AS" for 2000? ... I played around with GRANT but apparently, a member of "db_datareader" ... Reading Books Online tells us that to add extended properties, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Execute DTS Package from non-privileged VB Client
    ... the user having sysadmin privileges? ... the package will execute on the users machine under the users security context, so they will need rights perform the actions inside the package. ... They may also need the public role in msdb, which all users get by default, to load the package if stored in SQL Server. ... It may be better to devise an alternative process which means the job is executed on the server, which hopefully means another more privileged security context Perhaps it can be scheduled? ...
    (microsoft.public.sqlserver.dts)
  • Re: Msg Box and DTSRun
    ... runs on the machine which you execute it from. ... server you must executed it on the server, such as through a SQL Server ... It also runs under the security context of the user who calls ... the package, so from your desktop using Enterprise Manager or DTSRUN, ...
    (microsoft.public.sqlserver.dts)
  • Re: Extended stored procedures failues - only under sql server login
    ... regardless of the fact that its sql server login is ... member of sysadmin system role! ... role can execute the same extended stored procedures successfully. ...
    (microsoft.public.sqlserver.programming)