SQLTeam.com | Weblogs | Forums

Help with stored procedure


#1

Hope this is the right place to post.

I have this simple stored procedure I'm testing but it isn't updating the DB.

It's pretty straightforward, looking for help as to what I'm doing wrong.

Thanks

USE [DATA10];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [=updateCAD]
WITH
EXECUTE AS CALLER
AS
select FPARTNO ,FCCADFILE1
from INMAST
where FPARTNO = 'TEST1234'

begin
update INMAST set FCCADFILE1='1234ABCD.PDF' where FPARTNO = 'TEST1234'
end.


#2
  1. The caller doesn't have authority to UPDATE the table.
    and/or
  2. FPARTNO = 'TEST1234' does not exist in table "INMAST" in the default schema for the caller.

#3

how do I view the 'execute as' users to set perms etc?


#4

That depends on how security is set up and maintained on that instance. But in general, you look at the users in the db and match them to the user(s) executing the code. Keep in mind that sometimes a generic user makes the connection to SQL, so that even if "Joe" or "Mary" is actually using the screen, the SQL connection might always be made with user name "app1", for example.


#5

I got it figured out, thanks everyone.