SQL Agent Roles

Michelle A.


I have a domain account that is assigned the the SQLAgentOperator, SQLAgentReader, and SQLAgentUser roles. That domain account is able to create a job and delete the job. But, it is unable to edit the job. If I create a SQL Login with these same permissions, it is able to edit the job. What am I missing

We were looking forward to implementing these new built-in security roles because we had to use our own security roles to give us similar functionality in SQL 2000 but it doesn't seem to be working properly in 2005.

Michelle

Satya SKJ

See this http://www.sql-server-performance.com/faq/sqlviewfaq.aspx faqid=137 fyi.

What is the authentication method used to connect SQL Server

Michelle A.

Thank you for the article, unfortunately the domain accounts are not behaving as expected when assigning them to these roles which is why I posted my issue. The domain account is assigned to these roles and is able to create a job (the owner = the domain account). But it can't edit the job that it owns. It can start the job and delete the job but it can't see the status of the job when it is running, either. If I create a SQL Login and assign it to these same roles, it seems to behave as documented (it can edit jobs that it owns, etc.). We're using SQL Server and Windows Authentication mode.

Satya SKJ

IF the SQLAgent is not a part of sysadmin and that job is created by SA or any other login with SYSADMIN privileges then that login may not be able to modify the job as it is owned by SA. As referred in the article try to set the login under that role.

Michelle A.

The job was created by a domain account that is NOT in the sysadmin role. It is in the SQLAgentOperator role. The domain account can create the job and the owner shows that it is owned by that domain account yet that domain account can't perform the functions on the job as documented in BOL for this role. For example, instead of an 'edit' button, they have a 'view' button on the job step.

ALZDBA

it is a BUG ( more like a flaw ). We have the same issue.

But thanks to your thread, now we'll use the workaround and for the moment create a jobadmin-user and distribute it's credentials to those who are now jobadmin using their windows account.

Let's hope sp2 fixes this flaw !

Satya SKJ

Thanks Alzdba, if you think this is a bug please report it to Microsoft COnnect for a fix.

Michelle

For information please run SP_HELP_JOB and let me have the output.

Thanks.

Michelle A.

This is for one of the jobs in question. I don't think that it's a bug because in the Technet Newsgroup someone tried this out with a domain account of theirs and it worked as designed.

Core Job Details:
=================
job_id originating_server name enabled description start_step_id category owner notify_level_eventlog notify_level_email notify_level_netsend notify_level_page notify_email_operator notify_netsend_operator notify_page_operator delete_level date_created date_modified version_number last_run_date last_run_time last_run_outcome next_run_date next_run_time next_run_schedule_id current_execution_status current_execution_step current_retry_attempt has_step has_schedule has_target type
------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------- ------------------ -------------------- ----------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------ ----------------------- ----------------------- -------------- ------------- ------------- ---------------- ------------- ------------- -------------------- ------------------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------- ----------- ------------ ----------- -----------
88F1C87B-FFD0-43C1-B3FD-7A23BF8D271D AFNCCSQL1 AgentGoalLoad-Daily 1 No description available. 1 [Uncategorized (Local)] BLOOMINGTON\TiffanyBeltran 0 0 0 0 (unknown) (unknown) (unknown) 0 2007-02-06 22:03:55.857 2007-02-06 22:03:58.170 3 20070207 70000 1 20070208 70000 24 4 0 (unknown) 0 1 1 1 1


Job Steps:
==========
step_id step_name subsystem command flags cmdexec_success_code on_success_action on_success_step_id on_fail_action on_fail_step_id server database_name database_user_name retry_attempts retry_interval os_run_priority output_file_name last_run_outcome last_run_duration last_run_retries last_run_date last_run_time proxy_id
----------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------- -------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- ----------------- ---------------- ------------- ------------- -----------
1 Load Tables TSQL EXEC dbo.proctblDailyPerformanceINSAttendanceCard
EXEC dbo.proctblDailyPerformanceINSClick2Coach
EXEC dbo.proctblDailyPerformanceINSScheduleAdherence
EXEC dbo.proctblDailyPerformanceUPDAttendanceCard
EXEC dbo.proctblDailyPerformanceUPDClick2Coach
EXEC 0 ((normal)) 0 1 (quit with success) 0 2 (quit with failure) 0 NULL AgentGoal NULL 0 0 0 (normal) NULL 1 1658 0 20070207 70002 NULL


Job Schedules:
==============
schedule_id schedule_name enabled freq_type freq_interval freq_subday_type freq_subday_interval freq_relative_interval freq_recurrence_factor active_start_date active_end_date active_start_time active_end_time date_created schedule_description next_run_date next_run_time schedule_uid job_count
----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ------------- ---------------- -------------------- ---------------------- ---------------------- ----------------- --------------- ----------------- --------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------------------------------ -----------
24 Daily 7am 1 4 1 1 0 0 0 20070206 99991231 70000 235959 2007-02-06 22:03:58.433 (Description not requested.) 20070208 70000 97D3ABB2-FA03-4FD3-A679-037BA43E3DDF 1


Job Target Servers:
===================
server_id server_name enlist_date last_poll_date last_run_date last_run_time last_run_duration last_run_outcome last_outcome_message
----------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------- ------------- ------------- ----------------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 AFNCCSQL1 1998-11-13 00:00:00.000 1998-11-13 00:00:00.000 20070207 70000 1700 1 The job succeeded. The Job was invoked by Schedule 24 (Daily 7am). The last step to run was step 1 (Load Tables).

Satya SKJ

Looks ok to me, just on that aspect what is the service pack level of SQL 2005.

Michelle A.

This server is SQL Server 9.0.2153 Enterprise Edition. We have another one that is SQL Server 9.0.2047 Standard Edition. Both are 64-bit. I think that the 9.0.2153 has post-sp1 hot fixes. We've tried multiple client machines and two different domain accounts. This seems to work FINE with SQL Logins. Thanks for your help.

ALZDBA

Michelle A. wrote:

... I don't think that it's a bug because in the Technet Newsgroup someone tried this out with a domain account of theirs and it worked as designed. ...

I've tested it on multiple clients, multiple servers (SQL2005 Std (32bit) (2servers) and EE(64bit) all on sp1).

Our scenario is :

the domainaccount is member of the Windows Local administrators group. (not needed for sqlserver, but for some other stuff)

Builtin\Administrators is revoked from SQLServer. ( this can be a pitfall regarding authentication ! )

The domainaccount is member of SQLAgentOperatorRole, is member of db_owner group of some userdb and has no fixed serverrolemembership.

In this case the domainaccount can only modify jobs (even owned by itself) when he is member of the sysadmin fixed server role !

If the user is not member of SQLAgentOperatorRole, it can modify the job !, but cannot see other jobs (not owned by itself)

When using a SQLuser that is member of the SQLAgentOperatorRole, jobs can be modified when owned by that sqluser.

It is very strange, at one 32-bit server it works as designed, at all others the issue arises.
Until now, I'm still conviced both 32-bit servers are exactly installed and the 64-bit instances are also installed using the same checklist.

I'm still trying to figure out what is actualy wrong.

Michelle A.

ALZDBA wrote:

In this case the domainaccount can only modify jobs (even owned by itself) when he is member of the sysadmin fixed server role ! If the user is not member of SQLAgentOperatorRole, it can modify the job !, but cannot see other jobs (not owned by itself)

So, does that mean that if the domain acount is in the system administrator AND the SQLAgentOperator role that they can modify their own jobs but not see any others

Satya SKJ

SQLAgentOperatorRole members cannot change job ownership to gain access to jobs that they do not already own.

If that domain account is in SysAdmin then it can perform anything with scheduled jobs, that is what happening on my systems without any problem...works as expected.

Michelle A.

I added one of our SQLAgentOperatorRole domain accounts to the sysadmin server role and it does seem to have the sysadmin permissions (it can do anything to any job, regardless of the owner). So, maybe I don't understand the behavior that ALZDBA is seeing. If I do understand him correctly, I am unable to reproduce his issue. I still have my own issues with SQLAgentOperatorRole members unable to perform the functions as documented in BOL by that role if the member is a domain account.

These domain accounts do have some OS permissions (they're in the Distributed COM local group on the server) for working with SSIS packages. They probably have access to some file shares. But, they are not in the local administrators group (nor is the local administrators group in the sysadmin role on the SQL Server).

Michelle

Michelle A.

Satya,

The domain account that is in the SQLAgentOperatorRole is not trying to change the owner of a job that they own, they are trying to edit a step of a job that they own. But, instead of the edit button - they have a view button. I can take screen shots if that would help.

Michelle