Stored Procedure, error message, and SQL Server Job

Stored Procedure, error message, and SQL Server Job

Post by Michael Cap » Fri, 10 Mar 2000 04:00:00

I'm running into a problem with a stored procedure handling messages.

First, create a table with one integer column.
    Create table test (a int)

Next, create a unique index on that column using Enterprise Manager. Make
sure to check the checkbox "Ignore duplicate values".

Now insert 10 rows into the table with "a" being 1..10.

Create a stored procedure which will insert 5 into this table.

Call this stored procedure from Query Analyzer. You should receive message #
3604, Duplicate Key Ignored.

Now, create an SQL Server job which calls this stored procedure. Run this
job. The job's status will be "failed" because message #3604 is returned.
Yet, this is not really a failure.

Is there any way to get around this problem?


1. Security error message problem with SQL Server Stored procedures

Dear All

Please could you help?

I have created a number of stored procedures within SQL Server 7 and 2000 as SA.

I then granted execute permission on  the stored procedure to an application user
account that does not have permission on the underlying tables that the stored
procedure access and updates.

It is possible to run the stored procedure as SA and it works as it should
returning no error messages.

when you run the stored procedure as the application user account it works as it
should ( selects from and inserts into the underlying tables the user does not
have purmission to access) , but comes up with error messages pertaining to the
user not having select and insert permission on the underlying tables as and when
it performs its actions.

I want to be able to use stored procedures to grant execute on stored stored
procedures to a application user account, but not to grant select, update, insert
and delete on the underlying tables.

This is a problem because I want the stored procedures to be accessed by a number
of different middle where layers that think an error has occured.

I have tryed using the "SET ANSI_WARNINGS  OFF"  but this prevents the stored
procedure from running at all.
I have looked at the Microsoft MSDN April and I quote "Users can be granted
permission to execute a stored procedure even if they do not have permission to
execute the procedure's statements directly."
Microsoft's own Microsoft SQL Server training material also claim that stored
procedure are a way of implementing security to allow users to run stored
procedures that they have execute permission on but do not necessarily have
permission on the underlying tables.
Thanks in advance.
Kind regards James.

James Kirby
Additionality Ltd
1 Adam Street

2. migrating sql databases from sql 7.0 to sql 2000

3. Capturing SQL Server error messages from a stored procedure

4. How do I make a report start a new page for a new vendor-code ?

5. Stored Procedure in SQL 7.0 SP1 giving erounous error messages:

6. except

7. SQL 2000: How to transfer error message between store procedure

8. Should be simply

9. How to suppress SQL Server message in stored procedure

10. SP3 Issue: SQL Server Agent Jobs and running a Stored Procedure