Some kind of permissions on stored procedure?

Question

Post reply

Some kind of permissions on stored procedure?

pepsodenz

Newbie

Points: 3
More actions
April 20, 2006 at 9:33 pm

#117044

Hi,
I have a problem with my application (ACCESS 2000 front end and SQL server backend) where I can generate a report (I'm the one developed the report) but not other developers/users.
The report recordsource is set to a stored procedure in SQL server via pass through query.
The stored procedure contains three set of EXEC command to insert values into each local temporary table. There are no permissions or anything set on the SP nor temp tables.
My application will create the temp tables automatically when it doesnt exists. I have tried to drop those tables so the application will create a fresh one when other developers run it. But still no luck.
The funny thing is it runs properly if I run SP codes (copy and paste the codes inside the SP) in Query Analyzer.
But if I call the SP using Query Analyzer, it wouldnt work.
Is there anything I might have missed? This is the first time I use SP for my application.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

In what way 'does it not work' for others than you? Errors? Messages?

Who owns the proc? You or dbo?

In any case 'others' must have execute granted to them on the proc, else they'd be denied execute on it

/Kenneth

pepsodenz Newbie Points: 3 More actions · Answer 2

It shows me some records in the report but others received "No record available" message. Which means that the code that inserts records into the temp tables doesnt work.

I placed some print statements in the SP, it prints them without error messages but no records inserted.

dbo is the owner of the SP.

Any other SP or views used by this SP are owned by dbo.

Here is part of the SP codes.

-- Create a unique ID for this process

SET @uid = CONVERT (VARCHAR(255),NEWID())

SELECT @uid

-- Ensure there are no records with the newly generated ID in these tables

DELETE TempSummaryReport_Referrals WHERE uid = @uid

DELETE TempSummaryReport_Completed WHERE uid = @uid

DELETE TempSummaryReport_HomeVisit WHERE uid = @uid

print 'AAAA'

SET @sSQL = 'SELECT ''' + @uid + ''', getdate() , vwReportReferrals.PRRefByID,

([EmpLstNam] + '', '' + [EmpFstNam]) AS SignedBy,

dbo.getWeekNumber([PRRefDat]) AS Week,

CAST(dbo.getFirstDtOfTheWeek(dbo.getWeekNumber(PRRefDat),Year(PRRefDat)) AS VARCHAR(11)) + '' - '' + CAST(dateadd(day,4,dbo.getFirstDtOfTheWeek(dbo.getWeekNumber(PRRefDat),Year(PRRefDat))) AS VARCHAR(11)) AS RangeDt,

Count(vwReportReferrals.PRProgRefID) AS Total_Referrals

FROM vwReportReferrals INNER JOIN tblEmployee

ON vwReportReferrals.PRRefByID = tblEmployee.EmpID

WHERE (((DateDiff(day,[vwReportReferrals].[PRRefDat],''' + @startDate + '''))0))

AND PRProgRefID In (''3'',''6'',''9'',''11'')

GROUP BY

vwReportReferrals.PRRefByID,

([EmpLstNam] + '', '' + [EmpFstNam]),

dbo.getWeekNumber([PRRefDat]),

CAST(dbo.getFirstDtOfTheWeek(dbo.getWeekNumber(PRRefDat),Year(PRRefDat)) AS VARCHAR(11)) + '' - '' + CAST(dateadd(day,4,dbo.getFirstDtOfTheWeek(dbo.getWeekNumber(PRRefDat),Year(PRRefDat))) AS VARCHAR(11))'

SET DATEFIRST 1

INSERT TempSummaryReport_Referrals (UID,[Date], PRRefByID, SignedBy, [Week],RangeDt, Total_Referrals, [3],[6],[9],[11])

EXECUTE CrossTab @sSQL, 'Count(PRProgRefID)','PRProgRefID','',' AND PRProgRefID In (''3'',''6'',''9'',''11'')', 'vwReportReferrals' , ''

Conway Painting Right there with Babe Points: 785 More actions · Answer 3

Hi,

My first thought is that you have all the necessary permissions in the temp table, but your other users don't. As a first port of call, that's what I'd check.

All Best

Conway

pepsodenz Newbie Points: 3 More actions · Answer 4

Hi Conway and Ken

Thanks for your input.

I finally found the cause of the problem.

It was as simple as I parse in date in 10 characters long and when parsed in through the SP, it truncate it to varchar(8). oppps.

Cheers