March 24, 2005 at 12:56 pm
Maybe its just before a long weekend and I can't get my brain to work. I created a stored proc and a new databases role. I gave this role EXEC authority to the proc and added a new user to this role.
eg GRANT EXECUTE ON [dbo].[FetchNexteTriageEntry] TO [db_executor]
GO
This proc selects rows from a table called Published. I signed on with the user and tried to execute this proc and I get the error message
Server: Msg 229, Level 14, State 1, Line 1
SELECT permission denied on object 'Published', database 'eTriage', owner 'dbo'.
If I give permission to public on this table I can execute the stored proc but I can also SELECT rows from the table directly which I don't want. What am I missing?
Francis
March 24, 2005 at 1:13 pm
More info I think the erro has to do with dynamic SQL. I want to be able to return only x rows so I have coded:
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'FetchNexteTriageEntry'
AND type = 'P')
DROP PROC dbo.FetchNexteTriageEntry
GO
CREATE PROC FetchNexteTriageEntry @maxrows int = 1
AS
declare @sql nvarchar(2000)
SELECT @sql = N'SELECT '
+ CASE @maxrows
WHEN 0 then ''
ELSE N'TOP ' + convert(varchar(10), @maxrows)
END
+ N' e.PK_ID, e.optGender, e.TriageScoreFinal, e.triageScore'
N' FROM dbo.published p' +
N' JOIN dbo.etriage e WITH (NOLOCK) ON p.pk_ID =e.pk_ID '+
N' JOIN dbo.lookup_Site ls ON e.txtSiteA = ls.pk_Site ' +
N' LEFT OUTER JOIN dbo.lookup_OverRideReason lr ON e.cbbTriageScoreORExpl = lr.pk_OverRideReason '+
N' LEFT OUTER JOIN dbo.lookup_PrimComplaints lc ON e.txtTriageComplaint = lc.pk_ComplaintCode ' +
N' WHERE p.PublishedStatus = ''Q'' ' +
N' ORDER BY e.pk_ID'
EXEC sp_executesql @sql, N'@maxrows int', @maxrows
go
It appears to work the waay I want if I don't use dynamic SQL.
Francis
March 24, 2005 at 1:18 pm
You pretty much said it yourself. The user needs to have direct access to the table if you make the select in dynamic sql. Try this to solve your problem :
Declare @maxrows as int
set @maxrows = 10
Set rowcount @maxrows
select * from dbo.SysObjects order by name
set rowcount 0
This will allow you to use a select in a static proc instead of dynamic sql.
March 24, 2005 at 1:29 pm
Thanks
Francis
March 28, 2005 at 8:00 am
Fortunately, this is a workaround that we won't need to use with SQL Server 2005, where
DECLARE @MaxRows int
SET @MaxRows = 100
SELECT TOP (@MaxRows) ....
FROM ....
will work nicely.
Scott Thornburg
March 29, 2005 at 7:46 am
Now that's a nice enhancement. Thanks for the update Scott.
Francis
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply