November 10, 2005 at 4:44 am
Hi all,
I have created a stored procedure (as below). This procedure is used by an access front end with some VB scripting too.
The sp and all tables are owned by the dbo. The windows groups and users have been granted execute permissions on the sp.
The problem I am having is when the sp is run from access by a user in a windows group that has been granted execute permissions an error 208 appears.
However, if I change the owner of the sp to be my windows login, I can run it perfectly. The same happens if I change it to be explicitly owned by another user.
this is the sp...
CREATE PROCEDURE dbo.[qrrContract Certainty Extract](@report_start_date smalldatetime,
@report_end_date smalldatetime)
AS SELECT dbo.tblPolicy.PolicyReference AS [Policy Number], dbo.tblPolicy.InceptionDate AS [Inception Date], dbo.tblUnits.Description,
dbo.tblPolicyTracking.DateCoverNoteSent AS [Date Cover Note Sent]
FROM dbo.tblPolicy INNER JOIN
dbo.tblUnits ON dbo.tblPolicy.UnitID = dbo.tblUnits.UnitID AND dbo.tblPolicy.UnitID = dbo.tblUnits.UnitID INNER JOIN
dbo.tblPolicyTracking ON dbo.tblPolicy.PolicyID = dbo.tblPolicyTracking.PolicyID
WHERE (dbo.tblPolicy.InceptionDate >= @report_start_date) AND (dbo.tblPolicy.InceptionDate <= @report_end_date)
ORDER BY dbo.tblPolicy.InceptionDate, dbo.tblPolicy.PolicyReference
Any help will be greatly appreciated.
November 10, 2005 at 5:08 am
Accoring to the books online, error 208 = invalid object name
Are you specifying the owner of the stored procedure when executing it?
Like
EXECUTE dbo.USP_TESTING
instead of EXECUTE USP_TESTING (looks for an object myuser.USP_TESTING)
November 10, 2005 at 5:29 am
Great point thank you! I will have to check the VB in the background, I will let you know how I get on.
November 10, 2005 at 3:29 pm
Object that are owned by DBO should be visible to other users.
Sometimes invalid object name error comes up when users connect to the wrong database. It happens when the database name is not specified and connection is made to the default user's database.
Make sure this error 208 says that Object not found "dbo.[qrrContract Certainty Extract]" not another object.
Also check your code if somehow the SP name was without brackets because it has spaces. You may try to change name to the one without spaces, with underscores for example.
Yelena
Regards,Yelena Varsha
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply