April 26, 2014 at 2:37 am
i have a dbuser "girilimited"
he only need to use commnads insert and delete
so
i have give dbroles as db_datareader and db_datawriter
and i have removed access for update by using below command
DENY UPDATE on SCHEMA::dbo TO girilimited(dbuser)
if the user want to update any record he only use the procedure
so i have created procedure like
create procedure limitedupdate(@i varchar(max),@j int)
as
begin
set rowcount @j-2 ;
exec(@i)
set rowcount 0;
end
after i have given permission to execure the user that procedure...
use [RepTestDB]
GO
GRANT EXECUTE ON [dbo].[limitedupdate] (procedurename)
TO [girilimited](dbuser)
GO
then i executed the below query i am getting the below error..
exec limitedupdate 'update Child set pid=10 where id=101',1
Msg 229, Level 14, State 5, Line 1
The UPDATE permission was denied on the object 'Child', database 'RepTestDB', schema 'dbo'.
Please any one help on this..
To update any record The user use only that procedure...how can i give permissions to the user to achieve this..
Please share any info...
April 26, 2014 at 2:43 am
Have you looked into EXECUTE AS
😎
April 26, 2014 at 2:55 am
are u aksing me to execute like
exec as limitedupdate 'update Child set pid=10 where id=101',1
April 26, 2014 at 3:39 am
dastagiri16 (4/26/2014)
After my search i found the solution for that...and modified my procedure as below.lll
alter procedure limitedupdate(@i nvarchar(max),@j int)
WITH EXECUTE AS OWNER
as
begin
set rowcount @j-2 ;
execute sp_executesql @i
set rowcount 0;
end
That's what I had in mind
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply