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.|||Hi Jason,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
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.
=====================================================
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
>|||Hello Jason,
Sorry for my fault and I spoke too quick based on my memory. In SQLServer
2000, the following table describes the user context under which a
scheduled SQLServer Agent job is run depending on whether the owner of the
job is sysadmin in SQLServer or not.
Owner of job Job Type Job executed as (user)
=========== ========= =============
non-sysadmin TSQL [owner of Job]
non-sysadmin CmdExec/Activex [SQL Agent proxy account]
sysadmin TSQL [owner of Job]
sysadmin CmdExec/Activex [SQLServer startup account]
Also, scheduled DTS packages fall under CmdExec job type.
You are correct that the checkbox only blocks CmdExec and ActiveScripting
job of non-sysadmin users. They could still schedule and run jobs that
don't use CmdExec and ActiveScripting job etc.
Since the SQLagent will "impersonate" the user only when the user runs the
job step, actually there is no option to "deny" the user to access the
database objects that he has permissions in the first place.
However, you could deny the user to create jobs by the following method:
1. Add the user as a database user of msdb database.
2. Run the following statement to deny its permission to
use msdb
deny execute on msdb.dbo.sp_add_job to username
If you have any further questions or concerns, please feel free to let's
know. Thank you.
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.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Jason,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
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.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

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
>

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, ca
n
still create and schedule jobs under sql agent. I don’t want to allow thi
s,
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/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/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/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Jason,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
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.
========================================
=============
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 the
n
> 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 jo
b
> 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
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/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/subscript...rt/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/defaul...ProPhone#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 provid
e
> ONE BUSINESS DAY RESPONSE to all posts.
> If this response time does not meet your needs, please contact CSS for mor
e
> immediate assistance:
> [url]http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607[/ur
l]
>|||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 ca
n
> execute CmdExec and ActiveScripting job
> steps" check box in the sqlagent property dialog. If so, it's supposed tha
t
> 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 user
s
> 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
>|||Hello Jason,
Sorry for my fault and I spoke too quick based on my memory. In SQLServer
2000, the following table describes the user context under which a
scheduled SQLServer Agent job is run depending on whether the owner of the
job is sysadmin in SQLServer or not.
Owner of job Job Type Job executed as (user)
=========== ========= =============
non-sysadmin TSQL [owner of Job]
non-sysadmin CmdExec/Activex [SQL Agent proxy account]
sysadmin TSQL [owner of Job]
sysadmin CmdExec/Activex [SQLServer startup account]
Also, scheduled DTS packages fall under CmdExec job type.
You are correct that the checkbox only blocks CmdExec and ActiveScripting
job of non-sysadmin users. They could still schedule and run jobs that
don't use CmdExec and ActiveScripting job etc.
Since the SQLagent will "impersonate" the user only when the user runs the
job step, actually there is no option to "deny" the user to access the
database objects that he has permissions in the first place.
However, you could deny the user to create jobs by the following method:
1. Add the user as a database user of msdb database.
2. Run the following statement to deny its permission to
use msdb
deny execute on msdb.dbo.sp_add_job to username
If you have any further questions or concerns, please feel free to let's
know. Thank you.
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.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

A User connections question

Is there anyway or SQL script to configure the SQL server to automatically
log users out if their connection remains idle for a predetermined period of
time?
The convenience of allowing users to login to Great Plains in the morning
and staying there all day, even if they actually use the application for only
a fraction of the time, is too expensive to cater to.
I want to start by demanding that users logout of Great Plains when they
will not be using the application for a period of time (i.e. during lunch,
during coffee breaks, during meetings, while they are performing tasks not
requiring immediate access to Great Plains, etc.).
Thank You...
Hi
This is one of the things connection pooling would help with, but then you
will not be re-writting the package! I guess you could have a job that checks
the last_batch time in sysprocesses.
John
"clairvoyant316" wrote:

> Is there anyway or SQL script to configure the SQL server to automatically
> log users out if their connection remains idle for a predetermined period of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for only
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
|||clairvoyant316 wrote:
> Is there anyway or SQL script to configure the SQL server to automatically
> log users out if their connection remains idle for a predetermined period of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for only
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
Tried to post this yesterday, apparently it didn't "go". Here's a
script (and table) that I've used to handle this very problem in GP.
CREATE TABLE [dbo].[sb_IdleUsersRemoved] (
[iRowID] [int] IDENTITY (1, 1) NOT NULL ,
[iSPID] [int] NULL ,
[vchUserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[dtDateRemoved] [datetime] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE sb_RemoveIdleUsers
AS
DECLARE @.iSPID INT
DECLARE @.vchCommand VARCHAR(255)
/* Create list of SPIDs that have been idle for 12 hours, and are still
listed in dex_session */
SELECT a.spid, a.loginame
INTO #tempSPIDs
FROM master.dbo.sysprocesses a
INNER JOIN tempdb.dbo.dex_session b
ON a.spid = b.sqlsvr_spid
WHERE GETDATE() - a.last_batch > .5
UNION
SELECT spid, loginame
FROM master.dbo.sysprocesses
WHERE dbid IN (7, 16, 17) AND GETDATE() - last_batch > .5
BEGIN TRANSACTION
/* Record the list of spids and logins that are being removed */
INSERT INTO sb_custom.dbo.sb_IdleUsersRemoved
SELECT spid, loginame, GETDATE()
FROM #tempSPIDs
/* Delete records from dex_lock for any idle sessions */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id FROM tempdb.dbo.dex_session WHERE
sqlsvr_spid IN (SELECT spid FROM #tempSPIDs))
/* Delete records from dex_session for any idle sessions */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid IN (SELECT spid FROM #tempSPIDs)
COMMIT TRANSACTION
/* Kill SQL processes for idle sessions */
SELECT @.iSPID = 0
WHILE EXISTS(SELECT * FROM #tempSPIDs WHERE spid > @.iSPID)
BEGIN
SELECT TOP 1 @.iSPID = spid
FROM #tempSPIDs
WHERE spid > @.iSPID
ORDER BY spid
SELECT @.vchCommand = 'KILL ' + LTRIM(RTRIM(CONVERT(VARCHAR(10),
@.iSPID)))
EXEC (@.vchCommand)
END
DROP TABLE #tempSPIDs
/* Delete records from dex_lock for any records in dex_session that have
no active SQL connection */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
)
/* Delete records from dex_session that have no active SQL connection */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
GO
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks for your prompt response.
I ran that script and crashed the MS SQL 2000 with timeout as well as
corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
It didn't kick users out of the Great Plains at all.
Is there any condition or special configuration that I am not aware of to
run that script?
Thank You
"Tracy McKibben" wrote:

> Tried to post this yesterday, apparently it didn't "go". Here's a
> script (and table) that I've used to handle this very problem in GP.
|||clairvoyant316 wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
Not likely - the script I sent you simply creates a table and a stored
procedure.

> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
>
The script that I gave you creates a stored procedure. YOU must run
that stored procedure before it will remove any users. Surely you
realized that when you reviewed the script? Sounds like you might be
better off just leaving things alone.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi
tempdb is recreated when you restart SQL Server. If it was really corrupted
I would suspect a hardware failure rather than this script. There seems to be
a couple of table creation statements missing from the script and the
database names need changing on the four part names that are not tempdb.
John
"clairvoyant316" wrote:

> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
> Thank You
> "Tracy McKibben" wrote:
>
|||John Bell wrote:
> Hi
> tempdb is recreated when you restart SQL Server. If it was really corrupted
> I would suspect a hardware failure rather than this script. There seems to be
> a couple of table creation statements missing from the script and the
> database names need changing on the four part names that are not tempdb.
>
Yes, it's just a snippet of code from one of my production systems. I
made the assumption that the OP would be able to adapt it to his needs,
I assumed wrong.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This may
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:

> John Bell wrote:
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||

Quote:

Originally Posted by John Bell

Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This may
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:

> John Bell wrote:
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Interesting code, Tracy, but I am curious - what would become of a document or transaction that a user left open? I would guess that any unsaved changes would be lost, but the document itself is still retained. is that correct?
I do a fair amount of work in GP Modifier & VBA, plus SQL for my company. This is something we've considered doing in the past, to reduce the number of occupied licenses for clients.
Thanks! BTW, you have a neat site...I've bookmarked it.

A User connections question

Is there anyway or SQL script to configure the SQL server to automatically
log users out if their connection remains idle for a predetermined period of
time?
The convenience of allowing users to login to Great Plains in the morning
and staying there all day, even if they actually use the application for onl
y
a fraction of the time, is too expensive to cater to.
I want to start by demanding that users logout of Great Plains when they
will not be using the application for a period of time (i.e. during lunch,
during coffee breaks, during meetings, while they are performing tasks not
requiring immediate access to Great Plains, etc.).
Thank You...Hi
This is one of the things connection pooling would help with, but then you
will not be re-writting the package! I guess you could have a job that check
s
the last_batch time in sysprocesses.
John
"clairvoyant316" wrote:

> Is there anyway or SQL script to configure the SQL server to automatical
ly
> log users out if their connection remains idle for a predetermined period
of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for o
nly
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...|||clairvoyant316 wrote:
> Is there anyway or SQL script to configure the SQL server to automatical
ly
> log users out if their connection remains idle for a predetermined period
of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for o
nly
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
Tried to post this yesterday, apparently it didn't "go". Here's a
script (and table) that I've used to handle this very problem in GP.
CREATE TABLE [dbo].[sb_IdleUsersRemoved] (
[iRowID] [int] IDENTITY (1, 1) NOT NULL ,
[iSPID] [int] NULL ,
[vchUserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[dtDateRemoved] [datetime] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE sb_RemoveIdleUsers
AS
DECLARE @.iSPID INT
DECLARE @.vchCommand VARCHAR(255)
/* Create list of SPIDs that have been idle for 12 hours, and are still
listed in dex_session */
SELECT a.spid, a.loginame
INTO #tempSPIDs
FROM master.dbo.sysprocesses a
INNER JOIN tempdb.dbo.dex_session b
ON a.spid = b.sqlsvr_spid
WHERE GETDATE() - a.last_batch > .5
UNION
SELECT spid, loginame
FROM master.dbo.sysprocesses
WHERE dbid IN (7, 16, 17) AND GETDATE() - last_batch > .5
BEGIN TRANSACTION
/* Record the list of spids and logins that are being removed */
INSERT INTO sb_custom.dbo.sb_IdleUsersRemoved
SELECT spid, loginame, GETDATE()
FROM #tempSPIDs
/* Delete records from dex_lock for any idle sessions */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id FROM tempdb.dbo.dex_session WHERE
sqlsvr_spid IN (SELECT spid FROM #tempSPIDs))
/* Delete records from dex_session for any idle sessions */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid IN (SELECT spid FROM #tempSPIDs)
COMMIT TRANSACTION
/* Kill SQL processes for idle sessions */
SELECT @.iSPID = 0
WHILE EXISTS(SELECT * FROM #tempSPIDs WHERE spid > @.iSPID)
BEGIN
SELECT TOP 1 @.iSPID = spid
FROM #tempSPIDs
WHERE spid > @.iSPID
ORDER BY spid
SELECT @.vchCommand = 'KILL ' + LTRIM(RTRIM(CONVERT(VARCHAR(10),
@.iSPID)))
EXEC (@.vchCommand)
END
DROP TABLE #tempSPIDs
/* Delete records from dex_lock for any records in dex_session that have
no active SQL connection */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
)
/* Delete records from dex_session that have no active SQL connection */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
GO
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for your prompt response.
I ran that script and crashed the MS SQL 2000 with timeout as well as
corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
It didn't kick users out of the Great Plains at all.
Is there any condition or special configuration that I am not aware of to
run that script?
Thank You
"Tracy McKibben" wrote:

> Tried to post this yesterday, apparently it didn't "go". Here's a
> script (and table) that I've used to handle this very problem in GP.|||clairvoyant316 wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
Not likely - the script I sent you simply creates a table and a stored
procedure.

> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
>
The script that I gave you creates a stored procedure. YOU must run
that stored procedure before it will remove any users. Surely you
realized that when you reviewed the script? Sounds like you might be
better off just leaving things alone.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi
tempdb is recreated when you restart SQL Server. If it was really corrupted
I would suspect a hardware failure rather than this script. There seems to b
e
a couple of table creation statements missing from the script and the
database names need changing on the four part names that are not tempdb.
John
"clairvoyant316" wrote:

> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
> Thank You
> "Tracy McKibben" wrote:
>
>|||John Bell wrote:
> Hi
> tempdb is recreated when you restart SQL Server. If it was really corrupte
d
> I would suspect a hardware failure rather than this script. There seems to
be
> a couple of table creation statements missing from the script and the
> database names need changing on the four part names that are not tempdb.
>
Yes, it's just a snippet of code from one of my production systems. I
made the assumption that the OP would be able to adapt it to his needs,
I assumed wrong.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This ma
y
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:

> John Bell wrote:
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

A User connections question

Is there anyway or SQL script to configure the SQL server to automatically
log users out if their connection remains idle for a predetermined period of
time?
The convenience of allowing users to login to Great Plains in the morning
and staying there all day, even if they actually use the application for only
a fraction of the time, is too expensive to cater to.
I want to start by demanding that users logout of Great Plains when they
will not be using the application for a period of time (i.e. during lunch,
during coffee breaks, during meetings, while they are performing tasks not
requiring immediate access to Great Plains, etc.).
Thank You...clairvoyant316 wrote:
> Is there anyway or SQL script to configure the SQL server to automatically
> log users out if their connection remains idle for a predetermined period of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for only
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
Tried to post this yesterday, apparently it didn't "go". Here's a
script (and table) that I've used to handle this very problem in GP.
CREATE TABLE [dbo].[sb_IdleUsersRemoved] (
[iRowID] [int] IDENTITY (1, 1) NOT NULL ,
[iSPID] [int] NULL ,
[vchUserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[dtDateRemoved] [datetime] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE sb_RemoveIdleUsers
AS
DECLARE @.iSPID INT
DECLARE @.vchCommand VARCHAR(255)
/* Create list of SPIDs that have been idle for 12 hours, and are still
listed in dex_session */
SELECT a.spid, a.loginame
INTO #tempSPIDs
FROM master.dbo.sysprocesses a
INNER JOIN tempdb.dbo.dex_session b
ON a.spid = b.sqlsvr_spid
WHERE GETDATE() - a.last_batch > .5
UNION
SELECT spid, loginame
FROM master.dbo.sysprocesses
WHERE dbid IN (7, 16, 17) AND GETDATE() - last_batch > .5
BEGIN TRANSACTION
/* Record the list of spids and logins that are being removed */
INSERT INTO sb_custom.dbo.sb_IdleUsersRemoved
SELECT spid, loginame, GETDATE()
FROM #tempSPIDs
/* Delete records from dex_lock for any idle sessions */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id FROM tempdb.dbo.dex_session WHERE
sqlsvr_spid IN (SELECT spid FROM #tempSPIDs))
/* Delete records from dex_session for any idle sessions */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid IN (SELECT spid FROM #tempSPIDs)
COMMIT TRANSACTION
/* Kill SQL processes for idle sessions */
SELECT @.iSPID = 0
WHILE EXISTS(SELECT * FROM #tempSPIDs WHERE spid > @.iSPID)
BEGIN
SELECT TOP 1 @.iSPID = spid
FROM #tempSPIDs
WHERE spid > @.iSPID
ORDER BY spid
SELECT @.vchCommand = 'KILL ' + LTRIM(RTRIM(CONVERT(VARCHAR(10),
@.iSPID)))
EXEC (@.vchCommand)
END
DROP TABLE #tempSPIDs
/* Delete records from dex_lock for any records in dex_session that have
no active SQL connection */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
)
/* Delete records from dex_session that have no active SQL connection */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
GO
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||clairvoyant316 wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
Not likely - the script I sent you simply creates a table and a stored
procedure.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
>
The script that I gave you creates a stored procedure. YOU must run
that stored procedure before it will remove any users. Surely you
realized that when you reviewed the script? Sounds like you might be
better off just leaving things alone.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi
tempdb is recreated when you restart SQL Server. If it was really corrupted
I would suspect a hardware failure rather than this script. There seems to be
a couple of table creation statements missing from the script and the
database names need changing on the four part names that are not tempdb.
John
"clairvoyant316" wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
> Thank You
> "Tracy McKibben" wrote:
> > Tried to post this yesterday, apparently it didn't "go". Here's a
> > script (and table) that I've used to handle this very problem in GP.
>|||John Bell wrote:
> Hi
> tempdb is recreated when you restart SQL Server. If it was really corrupted
> I would suspect a hardware failure rather than this script. There seems to be
> a couple of table creation statements missing from the script and the
> database names need changing on the four part names that are not tempdb.
>
Yes, it's just a snippet of code from one of my production systems. I
made the assumption that the OP would be able to adapt it to his needs,
I assumed wrong.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This may
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:
> John Bell wrote:
> > Hi
> >
> > tempdb is recreated when you restart SQL Server. If it was really corrupted
> > I would suspect a hardware failure rather than this script. There seems to be
> > a couple of table creation statements missing from the script and the
> > database names need changing on the four part names that are not tempdb.
> >
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

a strange problem with RDA

Hello,

I have written a program for WinCE with .NET. In one of the forms, program gets data from the sql server with RDA. It works fine..the users get data.one two three.....but at 16th or 17th or 18th try an error occurs:

"SQL Mobile encounteres some problems..."

What does it mean? It gets data 15 times but after that it gets error....What is the problem with that?

I'm really confused...

Thanks in advance.

I tried to free the resources like RDA object and other SQL CE objects and it worked...

a search module.

okay so pretty much I am using the asp.net 2.0 membership/roles.

i wanna make a search box that users can type in a name and it will search the database. most likely in the table "aspnet_Users"

so when users search i want it to be like this:

cmd = select * from [table] WHERE username = textbox1.text (ofcourse with parameters instead of concatenation).

but i want it to have a "like" in there

so if the users type in "fenix" they should get results matching CLOSE to fenix. so

masfenix, fenix, fenxi, and you know related names. that are similar.

should I use LIKE? and if so HOW do i use that?

is it just gonna be

cmd = select * from [table] where Username LIKE @.username

@.username = textbox1.text

?

thanks

Hello my friend,

The LIKE clause uses the % wild character. Here are some examples: -

-- get countries beginning with c
select * from tblcountry where countryname like 'c%'

-- get countries ending with land
select * from tblcountry where countryname like '%land'

-- get countries containing the word 'land' or 'stan'
select * from tblcountry where countryname like '%land%' or countryname like '%stan%'

Kind regards

Scotty

|||

hi thanks for the answer

i knew about that before just thoguht there would be more solutions

|||

Hi,

Maybe the following link is helpful to you.

http://forums.asp.net/thread/1677621.aspx

Thanks.