RE: exec sp_help_job user account rights

Yes, great, this did the job.

Reagrds,
Vladimir

"Manpreet Singh" wrote:

I don’t know whether it work or not, but u can create a stored procedure
wrapper in which u can call original stored procedure sp_help_job using “WITH
EXECUTE AS “ clause and then give execute permission of external stored
procedure to user. The external stored procedure just pass the result of
internal one to user. :-)

Manpreet Singh
MCITP – Database Administrator, SQL Server 2005
MCITP – Database Developer, SQL server 2005
MCTS – SQL server 2005
MCTS – configuring SharePoint server 2007


"struchnjak" wrote:

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: Problem with the Legacy ASP files and the Sql Server Express
    ... It looks like it is not going to be an easy job getting it to work on the SQL Server 2005 and Express:(. ... 'Then you can execute the command and then retrieve the ID ... I am 100% sure this code works against any version of SQL Server, as long as the connection is OK and the SP has two parameters @varCompany as Input and @Return_Value as Output. ... I do not think your problem is due to difference of SQL Server2000 and SQL Server2005, unless your stored procedure has some thing that only works in SQL Server2000, not SQL Server 2005. ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: exec sp_help_job user account rights
    ... I don’t know whether it work or not, but u can create a stored procedure ... EXECUTE AS “ clause and then give execute permission of external stored ... Database Administrator, SQL Server 2005 ... the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns. ...
    (microsoft.public.sqlserver.security)
  • Re: Stored Procedure Security/Permissions
    ... Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000 ... > permission to read either table; ... > permission to execute the stored procedure, ...
    (microsoft.public.sqlserver.security)
  • Connection.Execute and SQL stored procedures
    ... ..asp, the connection object and MS SQL Server as the DB can help me out. ... Five of the six calls execute as expected. ... This particular stored procedure does quite a bit of work within the DB, ...
    (microsoft.public.sqlserver.odbc)
  • Re: How to get list of EventClasses in MSSQLServer2000
    ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
    (microsoft.public.sqlserver.security)