Hi,

My situation is to solve this problem with procedure.

For example this is my procedure then

create procedure getCustomer(
@strCustId as string
)
as
declare @sql as varcahr(100)
begin

select @sql=' select * from customer_master
where cust_id in ('+ @strCustId + ')'
Execute(@sql)
end


The value of the customer id will be like 1,4,6,8,10
and it will be execute from the front end like getCustomer '1,4,5,8,10'

So i have used dynamic statement.

as for as teh db_datareader role was there it was working.
Now they asked me to remove the that and give only public role..

shall i do anything with the procedure or any other way to solve this


Note: permission will not be given to the user to select rows from the
table.


Pls give me any solution ......

Thanks and regards
Sukumar



>From: "Medjamia, Mehenna" <Mehenna.[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] RE: Permission Problem
>Date: Fri, 30 Apr 2004 09:37:30 +0100
>
>Hi,
>
>Create a view and give read permission to the view.
>
>Hope this helps
>
>M&M
>
>-----Original Message-----
>From: Sukumar Krishnan [mailto:[Email Address Removed] 30 April 2004 08:00
>To: LazyDBA.com Discussion
>Subject: Reg: Permission Problem
>
>
>hi all,
>we are using dynamic sql statement in stored procedure.
>Like
>
>create procedure getCustomer(
>@strCustId as string
>)
>as
>declare @sql as varcahr(100)
>begin
>
> select @sql=' select * from customer_master
> where cust_id in ('+ @strCustId + ')'
> Execute(@sql)
>end
>
>
>
>When a user was given only exceute permissions to the stored procedures
>when he execute the procedure it shows error like select permission denied
>on the table customer_master.
>
>Note. We have given permission to execute the stored procedures
>The database role of the user is public.
>When we give the db_datareader permission it works fine. But unfortunately
>we cann't give the permission to the user.
>
>Please help me in this problem.....
>
>
>Thanks in advance
>
>regards
>Sukumar Krishan
>
>_________________________________________________________________
>Let your desktop sizzle! Get the hottest wallpapers.
>http://www.msn.co.in/entertainment/ Right here at MSN Entertainment!
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>Get today's cartoon: http://www.LazyDBA.com
>To unsubscribe, e-mail: mssqldba-[Email Address Removed] ,
>then respond to the confirmation email you get
>For additional commands, e-mail: mssqldba-[Email Address Removed] communication contains information which is confidential and may be
>legally privileged and is for the exclusive use of the addressee(s). If you
>are not a named addressee please contact the sender immediately, and delete
>the communication from your system. You are hereby notified that any
>disclosure, distribution or copying of this communication, in whole or in
>part, is strictly prohibited. Any views or opinions presented are solely
>those of the author and do not necessarily represent those of the Company,
>its directors or officers unless otherwise specifically stated. The Company
>is not responsible for any reliance placed on the information contained
>herein and excludes all liability.
>
>(c)Copyright 2003 Siemens Energy Services Limited. All Rights Reserved.
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>Get today's cartoon: http://www.LazyDBA.com
>To unsubscribe, e-mail: mssqldba-[Email Address Removed] ,
>then respond to the confirmation email you get
>For additional commands, e-mail: mssqldba-[Email Address Removed] brides & grooms FREE! http://www.shaadi.com/ptnr.php?ptnr=hmltag
Only on www.shaadi.com. Register now!

MS Sql Server LazyDBA home page