Ability to create and schedule jobs on shared system

I have a shared SQL Server, used by applications that don’t need an
instance, just a DB. Users with DBO rights to their DB, no system roles, can
still create and schedule jobs under sql agent. I don’t want to allow this,
how can I prevent it?
Hello Jason,
You could use sqlagent proxy account to run the jobs if they are not in
sysadmin server role. If you want to prevent it, you could use the
following method:
1. Start SQL Server Enterprise Manager.
2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then
expand SQL Server Group.
3. Expand the instance of SQL Server that you want to set up the proxy
account for.
4. Expand Management, right-click SQL Server Agent, and then click
Properties.
5. In the SQL Server Agent Properties dialog box, click the Job System
tab.
6. Under Non-SysAdmin job step proxy account, click to check the Only
users with SysAdmin priviledges can execute CmdExec and ActiveScripting job
steps check box.
If you have any further questions on the issue, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||i looked at this setting, it currently is checked, however there is a user w/
only dbo rights to their db that can create, schedule jobs and run jobs.
i checked the roles, he has none.
this is a windows account, it if has some high level, domain admin type
right in AD, could AD be working behind the scenes to enable the features in
SQL Server for this user?
""Peter YangMSFT]"" wrote:

> Hello Jason,
> You could use sqlagent proxy account to run the jobs if they are not in
> sysadmin server role. If you want to prevent it, you could use the
> following method:
> 1. Start SQL Server Enterprise Manager.
> 2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then
> expand SQL Server Group.
> 3. Expand the instance of SQL Server that you want to set up the proxy
> account for.
> 4. Expand Management, right-click SQL Server Agent, and then click
> Properties.
> 5. In the SQL Server Agent Properties dialog box, click the Job System
> tab.
> 6. Under Non-SysAdmin job step proxy account, click to check the Only
> users with SysAdmin priviledges can execute CmdExec and ActiveScripting job
> steps check box.
> If you have any further questions on the issue, please feel free to let's
> know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
|||Hello Jason,
Yes. If it's domain admin, it's by default in the group local admin groups.
Local admin group is added in SQL server with sysadmin rights by default.
You may want to check the logins on the server to check this.
You may want to temporarily remove the login to test if the issue still
occurs. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Please note that the newsgroups are staffed weekdays with a goal to provide
ONE BUSINESS DAY RESPONSE to all posts.
If this response time does not meet your needs, please contact CSS for more
immediate assistance:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
|||i have found another user who is NOT a domain admin, OR server admin. he is
DBO on his db and is able to create/run a job that runs a select from the
database.
i guess becuase these to meet the criteria of that checkbox.
that checkbox you mentioned, what actually is considered a cmdexec step?
assuming that is the case, is there any other way to block users from
creating jobs?
""Peter YangMSFT]"" wrote:

> Hello Jason,
> Yes. If it's domain admin, it's by default in the group local admin groups.
> Local admin group is added in SQL server with sysadmin rights by default.
> You may want to check the logins on the server to check this.
> You may want to temporarily remove the login to test if the issue still
> occurs. Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Please note that the newsgroups are staffed weekdays with a goal to provide
> ONE BUSINESS DAY RESPONSE to all posts.
> If this response time does not meet your needs, please contact CSS for more
> immediate assistance:
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
>
|||Hello Jason,
I'd like to confirm if check the "Only users with SysAdmin priviledges can
execute CmdExec and ActiveScripting job
steps" check box in the sqlagent property dialog. If so, it's supposed that
a domain user without sysadmin rights shall be able to create job but the
job cannot run properly because there is no proxy agent account to run the
job.
If you have proxy account configured, you are not able to block only one
user/login using the proxy account because it's used as proxy for all users
without sysadmin rights.
I think cmdexec step in a job is actually run as the proxy account.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
|||that check box is checked.
upon further testing, this is what I see.
only some commands are blocked as indicated by the descriptive text.
e.g. a user can create and run a job that does a simple select. but user
cannot run a job that kicks off a DTS package as it requires a cmdexec cmd,
he can create it though. I was wanting to block the running off all jobs to
force users to schedule them through a DBA.
""Peter YangMSFT]"" wrote:

> Hello Jason,
> I'd like to confirm if check the "Only users with SysAdmin priviledges can
> execute CmdExec and ActiveScripting job
> steps" check box in the sqlagent property dialog. If so, it's supposed that
> a domain user without sysadmin rights shall be able to create job but the
> job cannot run properly because there is no proxy agent account to run the
> job.
> If you have proxy account configured, you are not able to block only one
> user/login using the proxy account because it's used as proxy for all users
> without sysadmin rights.
> I think cmdexec step in a job is actually run as the proxy account.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>