January 2, 2002 at 1:32 pm
Iam giving permisions to the users to execute procedure which reads and writes
the data into the table. And the users cannot directly update the tables.
It works if stored procedure code doesnot have dynamic SQL but gives permision problems when the code contains dynamic SQL .
One limitation of ownership chains is the exec() call. While normal statements in procedures will work with ownership chains, the following one won't
alter proc lsp_deleteorders @OrderID int
as
begin
declare @sql varchar(200)
set @sql = 'update orders set active = 0 where OrderID = ' + convert(varchar, @OrderID)
exec(@SQL)
end
Can anyone tell the workoaround for this.
Thanks,
Sajai
January 2, 2002 at 1:46 pm
If you are going to use Dynamic SQL queries, either through EXEC[UTE] or xp_executesql, you're going to have to give permissions to the tables. There's no workaround for this. The reason being is that the SQL statements to be executed are checked at run-time, and in the context of the user running the statement, not the owner of the stored procedure. From Books Online:
quote:
Permissions to use the statement(s) within the EXECUTE string are checked at the time EXECUTE is encountered, even if the EXECUTE statement is included within a stored procedure. When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure.
However, with respect to your current stored procedure, couldn't you do the following?
ALTER PROC lsp_deleteorders
@OrderID int
AS
BEGIN
UPDATE Orders
SET active = 0
WHERE OrderID = CONVERT(varchar, @OrderID)
END
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 2, 2002 at 3:15 pm
January 3, 2002 at 11:11 am
Thx for the responses.
well the statement is not that simple. Actually i took some sample example. But i cant apply that workaround for a stmt like this.
CREATE PROCEDURE P1(@id int,@Fieldname varchar(25),@Fieldvalue text,@Fieldtype char(1))
as
Begin
set nocount on
Declare @string nvarchar(500)
Declare @ptrval varbinary(16)
if @Fieldtype='I'
Begin
set @string = 'Update table1' +
' set ' + @Fieldname + '=' + cast(@Fieldvalue as varchar(500))+
' where id=' + cast(@id as varchar(10))
Exec sp_executesql @string
End
Else If @Fieldtype='C'
Begin
set @string = 'Update table1' +
' set ' + @Fieldname + '=' + '''' + cast(@Fieldvalue as varchar(500)) + '''' +
' where id=' + cast(@id as varchar(10))
Exec sp_executesql @string
End
Else If @Fieldtype='T'
Begin
Select @ptrval = TEXTPTR(Info_Messagetext) from Email_Info
where id=@id
WRITETEXT Table1.Info_Messagetext @ptrval @Fieldvalue
End
if (@@error<>0)
return 0
else
return 1
set nocount off
May I think i should go with case stmts and then build the static SQL than dynamic SQL.
Thx,
Krishnan.
January 3, 2002 at 11:31 am
I try to avoid dyanmic sql whre possible. I choose case statements to call stored procedures for various queries.
It avoids the problems with owernship chains.
Steve Jones
January 7, 2002 at 11:17 am
You broke the ownership change by using dynamic SQL. Try the following:
create proc lsp_deleteorders @OrderID int
as
Update orders
set active = 0
where OrderID = @orderID
You will want to add the appropriate error checking, but this will eliminate having to grant the users access to the base tables...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply