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.

AAArgh! sp_addlinkedserver giving me fits

I seem to be getting conflicting error messages from sp_addlinkedserver.
Here is the problem:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.srvproduct=N' SQL Server'
gives me this:
Msg 15426, Level 16, State 1, Procedure sp_addlinkedserver, Line 44
You must specify a provider name with this set of properties.
OK, I'll specify a provider name:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.srvproduct=N' SQL Server',
@.provider=N'SQLNCLI'
gives me this:
Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
You cannot specify a provider or any properties for product 'SQL
Server'.
So I *must* specify a provider, and yet I *cannot* specify a provider?
OK, I'll take out the product name:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.provider=N'SQ LNCLI'
gives:
Msg 15429, Level 16, State 1, Procedure sp_addlinkedserver, Line 72
'(null)' is an invalid product name.
So null is an invalid product name if the provider is SQLNCLI. (I also
tried SQLOLEDB since the server is SQL 2000.) That goes against the
sp_addlinkedserver doc in BOL, which shows in the third "row" of the
table under "Remarks" that for a specific instance, the product name
doesn't need to be provided.
If the doc is right, then why the error message 15429?
What gives here?
The underlying problem is that I am trying to set up a kind of alias for
a linked server, so that from the production server, I can get some data
from another (linked) production server; from the test server, I can get
the data from the first production server. The test server can't see
the second production server.
Maybe there is a better way to do this... but I don't see it. (I don't
have access to set up a real alias in the client network utility on the
production server.)
The target databases are SQL 2000.
Thanks for any help.
David Walker
David,
Here is what I do.
EXEC master.dbo.sp_addlinkedserver @.server = N'RamaLama',
@.srvproduct=N'SrvrName', @.provider=N'SQLOLEDB', @.datasrc=N'SrvrName',
@.catalog=N'databasename'
Note that I have the @.srvproduct and the @.datasrc set to the same value.
Once this resolved my problem, I did not keep fooling with it to see if
there was another way.
RLF
"DWalker" <none@.none.com> wrote in message
news:uXkDw8BuHHA.4972@.TK2MSFTNGP05.phx.gbl...
>I seem to be getting conflicting error messages from sp_addlinkedserver.
> Here is the problem:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.srvproduct=N' SQL Server'
> gives me this:
> Msg 15426, Level 16, State 1, Procedure sp_addlinkedserver, Line 44
> You must specify a provider name with this set of properties.
> OK, I'll specify a provider name:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.srvproduct=N' SQL Server',
> @.provider=N'SQLNCLI'
> gives me this:
> Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
> You cannot specify a provider or any properties for product 'SQL
> Server'.
> So I *must* specify a provider, and yet I *cannot* specify a provider?
> OK, I'll take out the product name:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.provider=N'SQ LNCLI'
> gives:
> Msg 15429, Level 16, State 1, Procedure sp_addlinkedserver, Line 72
> '(null)' is an invalid product name.
> So null is an invalid product name if the provider is SQLNCLI. (I also
> tried SQLOLEDB since the server is SQL 2000.) That goes against the
> sp_addlinkedserver doc in BOL, which shows in the third "row" of the
> table under "Remarks" that for a specific instance, the product name
> doesn't need to be provided.
> If the doc is right, then why the error message 15429?
> What gives here?
> The underlying problem is that I am trying to set up a kind of alias for
> a linked server, so that from the production server, I can get some data
> from another (linked) production server; from the test server, I can get
> the data from the first production server. The test server can't see
> the second production server.
> Maybe there is a better way to do this... but I don't see it. (I don't
> have access to set up a real alias in the client network utility on the
> production server.)
> The target databases are SQL 2000.
> Thanks for any help.
> David Walker
|||"Russell Fields" <russellfields@.nomail.com> wrote in news:#dNp7kCuHHA.576
@.TK2MSFTNGP03.phx.gbl:

> David,
> Here is what I do.
> EXEC master.dbo.sp_addlinkedserver @.server = N'RamaLama',
> @.srvproduct=N'SrvrName', @.provider=N'SQLOLEDB', @.datasrc=N'SrvrName',
> @.catalog=N'databasename'
> Note that I have the @.srvproduct and the @.datasrc set to the same value.
> Once this resolved my problem, I did not keep fooling with it to see if
> there was another way.
> RLF
But I need for the data source to have an instance name in one server, and
none in another server. @.srvproduct is supposed to be "SQL Server" (or
"Any" for some provider names).
I'm not sure how you got away with giving a server NAME as the name of the
product.
David
|||David,
Did Peter Yang's answer resolve things for you? Blank is good, but
@.srvproduct is whatever you say it is.
If, however, you say it is 'SQL Server', you are predefining that the
@.server must be a physical name (or instance name) of the server that you
are trying to reach. Since you want @.server to be an alias (an excellent
idea for deploying from a development to a production environment) then you
will user a provider. Since you are SQL 2000, I used SQLOLEDB.
Out of curiousity, did you try it?
RLF
"DWalker" <none@.none.com> wrote in message
news:%231JXBLPuHHA.2752@.TK2MSFTNGP06.phx.gbl...
> "Russell Fields" <russellfields@.nomail.com> wrote in news:#dNp7kCuHHA.576
> @.TK2MSFTNGP03.phx.gbl:
>
> But I need for the data source to have an instance name in one server, and
> none in another server. @.srvproduct is supposed to be "SQL Server" (or
> "Any" for some provider names).
> I'm not sure how you got away with giving a server NAME as the name of the
> product.
> David
>
|||"Russell Fields" <russellfields@.nomail.com> wrote in
news:udbmMvYuHHA.4540@.TK2MSFTNGP05.phx.gbl:

> David,
> Did Peter Yang's answer resolve things for you? Blank is good, but
> @.srvproduct is whatever you say it is.
> If, however, you say it is 'SQL Server', you are predefining that the
> @.server must be a physical name (or instance name) of the server that
> you are trying to reach. Since you want @.server to be an alias (an
> excellent idea for deploying from a development to a production
> environment) then you will user a provider. Since you are SQL 2000, I
> used SQLOLEDB.
> Out of curiousity, did you try it?
> RLF
Sorry, I was out for a while.
@.srvproduct can be anything, but has to be supplied, and if it's "SQL
Server" then it has to be a real name.... hmmmm...
I find that the only way I can connect to a remote SQL 2000 server from
SQL 2005 management studio is to use Peter's example, and supply '' or
'test' or something like that for the server name. If I use 'SQL
Server', then I get a "logon failed for user xyz" (message 18456).
I'll use 'sql' for the product name.
Thanks to you both, including Peter.
David Walker

AAArgh! sp_addlinkedserver giving me fits

I seem to be getting conflicting error messages from sp_addlinkedserver.
Here is the problem:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.srv
product=N'SQL Server'
gives me this:
Msg 15426, Level 16, State 1, Procedure sp_addlinkedserver, Line 44
You must specify a provider name with this set of properties.
OK, I'll specify a provider name:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.srv
product=N'SQL Server',
@.provider=N'SQLNCLI'
gives me this:
Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
You cannot specify a provider or any properties for product 'SQL
Server'.
So I *must* specify a provider, and yet I *cannot* specify a provider?
OK, I'll take out the product name:
Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
@.Datasrc=N'ServerName\InstanceName',@.pro
vider=N'SQLNCLI'
gives:
Msg 15429, Level 16, State 1, Procedure sp_addlinkedserver, Line 72
'(null)' is an invalid product name.
So null is an invalid product name if the provider is SQLNCLI. (I also
tried SQLOLEDB since the server is SQL 2000.) That goes against the
sp_addlinkedserver doc in BOL, which shows in the third "row" of the
table under "Remarks" that for a specific instance, the product name
doesn't need to be provided.
If the doc is right, then why the error message 15429?
What gives here?
The underlying problem is that I am trying to set up a kind of alias for
a linked server, so that from the production server, I can get some data
from another (linked) production server; from the test server, I can get
the data from the first production server. The test server can't see
the second production server.
Maybe there is a better way to do this... but I don't see it. (I don't
have access to set up a real alias in the client network utility on the
production server.)
The target databases are SQL 2000.
Thanks for any help.
David WalkerDavid,
Here is what I do.
EXEC master.dbo.sp_addlinkedserver @.server = N'RamaLama',
@.srvproduct=N'SrvrName', @.provider=N'SQLOLEDB', @.datasrc=N'SrvrName',
@.catalog=N'databasename'
Note that I have the @.srvproduct and the @.datasrc set to the same value.
Once this resolved my problem, I did not keep fooling with it to see if
there was another way.
RLF
"DWalker" <none@.none.com> wrote in message
news:uXkDw8BuHHA.4972@.TK2MSFTNGP05.phx.gbl...
>I seem to be getting conflicting error messages from sp_addlinkedserver.
> Here is the problem:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.srv
product=N'SQL Server'
> gives me this:
> Msg 15426, Level 16, State 1, Procedure sp_addlinkedserver, Line 44
> You must specify a provider name with this set of properties.
> OK, I'll specify a provider name:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.srv
product=N'SQL Server',
> @.provider=N'SQLNCLI'
> gives me this:
> Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
> You cannot specify a provider or any properties for product 'SQL
> Server'.
> So I *must* specify a provider, and yet I *cannot* specify a provider?
> OK, I'll take out the product name:
> Exec master.dbo.sp_addlinkedserver @.Server=N'SrvAlias',
> @.Datasrc=N'ServerName\InstanceName',@.pro
vider=N'SQLNCLI'
> gives:
> Msg 15429, Level 16, State 1, Procedure sp_addlinkedserver, Line 72
> '(null)' is an invalid product name.
> So null is an invalid product name if the provider is SQLNCLI. (I also
> tried SQLOLEDB since the server is SQL 2000.) That goes against the
> sp_addlinkedserver doc in BOL, which shows in the third "row" of the
> table under "Remarks" that for a specific instance, the product name
> doesn't need to be provided.
> If the doc is right, then why the error message 15429?
> What gives here?
> The underlying problem is that I am trying to set up a kind of alias for
> a linked server, so that from the production server, I can get some data
> from another (linked) production server; from the test server, I can get
> the data from the first production server. The test server can't see
> the second production server.
> Maybe there is a better way to do this... but I don't see it. (I don't
> have access to set up a real alias in the client network utility on the
> production server.)
> The target databases are SQL 2000.
> Thanks for any help.
> David Walker|||"Russell Fields" <russellfields@.nomail.com> wrote in news:#dNp7kCuHHA.576
@.TK2MSFTNGP03.phx.gbl:

> David,
> Here is what I do.
> EXEC master.dbo.sp_addlinkedserver @.server = N'RamaLama',
> @.srvproduct=N'SrvrName', @.provider=N'SQLOLEDB', @.datasrc=N'SrvrName',
> @.catalog=N'databasename'
> Note that I have the @.srvproduct and the @.datasrc set to the same value.
> Once this resolved my problem, I did not keep fooling with it to see if
> there was another way.
> RLF
But I need for the data source to have an instance name in one server, and
none in another server. @.srvproduct is supposed to be "SQL Server" (or
"Any" for some provider names).
I'm not sure how you got away with giving a server NAME as the name of the
product.
David|||Hello David,
You may want to try the following statement to see if it works on your side:
EXEC sp_addlinkedserver
@.server='linkedservername',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='servername\instancename'
If you have any update, 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.|||David,
Did Peter Yang's answer resolve things for you? Blank is good, but
@.srvproduct is whatever you say it is.
If, however, you say it is 'SQL Server', you are predefining that the
@.server must be a physical name (or instance name) of the server that you
are trying to reach. Since you want @.server to be an alias (an excellent
idea for deploying from a development to a production environment) then you
will user a provider. Since you are SQL 2000, I used SQLOLEDB.
Out of curiousity, did you try it?
RLF
"DWalker" <none@.none.com> wrote in message
news:%231JXBLPuHHA.2752@.TK2MSFTNGP06.phx.gbl...
> "Russell Fields" <russellfields@.nomail.com> wrote in news:#dNp7kCuHHA.576
> @.TK2MSFTNGP03.phx.gbl:
>
> But I need for the data source to have an instance name in one server, and
> none in another server. @.srvproduct is supposed to be "SQL Server" (or
> "Any" for some provider names).
> I'm not sure how you got away with giving a server NAME as the name of the
> product.
> David
>|||"Russell Fields" <russellfields@.nomail.com> wrote in
news:udbmMvYuHHA.4540@.TK2MSFTNGP05.phx.gbl:

> David,
> Did Peter Yang's answer resolve things for you? Blank is good, but
> @.srvproduct is whatever you say it is.
> If, however, you say it is 'SQL Server', you are predefining that the
> @.server must be a physical name (or instance name) of the server that
> you are trying to reach. Since you want @.server to be an alias (an
> excellent idea for deploying from a development to a production
> environment) then you will user a provider. Since you are SQL 2000, I
> used SQLOLEDB.
> Out of curiousity, did you try it?
> RLF
Sorry, I was out for a while.
@.srvproduct can be anything, but has to be supplied, and if it's "SQL
Server" then it has to be a real name.... hmmmm...
I find that the only way I can connect to a remote SQL 2000 server from
SQL 2005 management studio is to use Peter's example, and supply '' or
'test' or something like that for the server name. If I use 'SQL
Server', then I get a "logon failed for user xyz" (message 18456).
I'll use 'sql' for the product name.
Thanks to you both, including Peter.
David Walker

A Problem in TSQL

Hi ALL,
I'd like to have a help to the problem.
If I make a SELECT as:
select * from [LS].[msdb].[dbo].[sysfiles]
(hier LS is a linked server) it working fine.
The following lines dont work:
DECLARE @.STR NVARCHAR ( 300 )
SELECT @.STR =3D 'select * from [LS].[msdb].[dbo].[sysfiles]'
EXECUTE @.STR
I get the message:
Server: Nachr.-Nr. 203, Schweregrad 16, Status 2, Zeile 3
Der Name 'SELECT * FROM [LS].[msdb].[dbo].[sysfiles]' ist
kein g=FCltiger Bezeichner.
Whot is wrong?=20
How can I correct it?
Regards
Simon
Hi,
Enclose the varible in parenthesis execute (@.STR)
DECLARE @.STR NVARCHAR ( 300 )
SELECT @.STR = 'select * from [LS].[msdb].[dbo].[sysfiles]'
execute(@.STR)
Or use the below query using sp_executesql
DECLARE @.STR NVARCHAR ( 300 )
SELECT @.STR = 'select * from [LS].[msdb].[dbo].[sysfiles]'
exec sp_executesql @.STR
Thanks
Hari
MCDBA
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:2ae0c01c46805$524e12b0$a501280a@.phx.gbl...
Hi ALL,
I'd like to have a help to the problem.
If I make a SELECT as:
select * from [LS].[msdb].[dbo].[sysfiles]
(hier LS is a linked server) it working fine.
The following lines dont work:
DECLARE @.STR NVARCHAR ( 300 )
SELECT @.STR = 'select * from [LS].[msdb].[dbo].[sysfiles]'
EXECUTE @.STR
I get the message:
Server: Nachr.-Nr. 203, Schweregrad 16, Status 2, Zeile 3
Der Name 'SELECT * FROM [LS].[msdb].[dbo].[sysfiles]' ist
kein gltiger Bezeichner.
Whot is wrong?
How can I correct it?
Regards
Simon

A new @@Error question.

Here is what I really need to accomplish:
CREATE TABLE [dbo].[Table1] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare @.Error1 int
declare @.Error2 int
begin transaction
drop table Table1
select @.Error1 = @.@.Error
exec sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0
commit transaction
else
rollback transaction
Im not understanding why Table1 still gets dropped? It seems to me like
@.@.Error2 should not be 0, therefore a Rollback should occur? Why is @.@.Error2
not 0?
TIA,
ChrisR
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
@.@.ERROR returns the error number of the last Transact-SQL statement
executed. You need to also check the proc return code to determine whether
or not the rename succeeded:
declare @.Error1 int
declare @.Error2 int
declare @.ReturnCode int
begin transaction
drop table Table1x
select @.Error1 = @.@.Error
exec @.ReturnCode = sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0 AND @.ReturnCode = 0
commit transaction
else
rollback transaction
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A3F5C0A-9EC9-492D-9205-04031D36BFAA@.microsoft.com...
> Here is what I really need to accomplish:
> CREATE TABLE [dbo].[Table1] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table2] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare @.Error1 int
> declare @.Error2 int
> begin transaction
> drop table Table1
> select @.Error1 = @.@.Error
> exec sp_rename 'Table3','Table1'
> select @.Error2 = @.@.Error
> if @.Error1 = 0
> if @.Error2 = 0
> commit transaction
> else
> rollback transaction
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
> --
> TIA,
> ChrisR
|||Look at the procedure code. Then look at the documentation regarding the
raiserror function. Then look at the documentation for @.@.error. The
following demonstrates why this happens. As Dan suggested, you also need
to refer to the documentation of the procedure to know how to use it.
set nocount on
go
create procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror with following statement', @.sev, 20)
return 1
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
alter procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror, no following statement', @.sev, 20)
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
drop procedure xx_test
go

A new @@Error question.

Here is what I really need to accomplish:
CREATE TABLE [dbo].[Table1] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare @.Error1 int
declare @.Error2 int
begin transaction
drop table Table1
select @.Error1 = @.@.Error
exec sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0
commit transaction
else
rollback transaction
Im not understanding why Table1 still gets dropped? It seems to me like
@.@.Error2 should not be 0, therefore a Rollback should occur? Why is @.@.Error2
not 0?
--
TIA,
ChrisR> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
@.@.ERROR returns the error number of the last Transact-SQL statement
executed. You need to also check the proc return code to determine whether
or not the rename succeeded:
declare @.Error1 int
declare @.Error2 int
declare @.ReturnCode int
begin transaction
drop table Table1x
select @.Error1 = @.@.Error
exec @.ReturnCode = sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0 AND @.ReturnCode = 0
commit transaction
else
rollback transaction
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A3F5C0A-9EC9-492D-9205-04031D36BFAA@.microsoft.com...
> Here is what I really need to accomplish:
> CREATE TABLE [dbo].[Table1] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table2] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare @.Error1 int
> declare @.Error2 int
> begin transaction
> drop table Table1
> select @.Error1 = @.@.Error
> exec sp_rename 'Table3','Table1'
> select @.Error2 = @.@.Error
> if @.Error1 = 0
> if @.Error2 = 0
> commit transaction
> else
> rollback transaction
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
> --
> TIA,
> ChrisR|||Look at the procedure code. Then look at the documentation regarding the
raiserror function. Then look at the documentation for @.@.error. The
following demonstrates why this happens. As Dan suggested, you also need
to refer to the documentation of the procedure to know how to use it.
set nocount on
go
create procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror with following statement', @.sev, 20)
return 1
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
alter procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror, no following statement', @.sev, 20)
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
drop procedure xx_test
go