Access to execute Store procedures

Hello there
I would like to give my users permission to insert/update/delete data
from/to tables and views
and i also need to give them full access to execute Store Procedures.
In order to do so i gave them on Dababase mode db_datareader and
db_datawriter roles.
This roles are giving them full access to insert/update and delete data, but
it doesn't give them access to execute Store procedures.
Which permission i sould give them wituout give them db_owner?Roy
GRANT EXECUTE ON storeprocedure TO username
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eNhG2daLHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Hello there
> I would like to give my users permission to insert/update/delete data
> from/to tables and views
> and i also need to give them full access to execute Store Procedures.
> In order to do so i gave them on Dababase mode db_datareader and
> db_datawriter roles.
> This roles are giving them full access to insert/update and delete data,
> but it doesn't give them access to execute Store procedures.
> Which permission i sould give them wituout give them db_owner?
>|||Shalom Uri
This i've already know
I'm looking for role for this and not go spesific on each store procedure.
on the business i'm working on they are afraid to give the users db_owner's
permission which i know that it is not a problem.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O%23XjUuaLHHA.1280@.TK2MSFTNGP04.phx.gbl...
> Roy
> GRANT EXECUTE ON storeprocedure TO username
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:eNhG2daLHHA.4376@.TK2MSFTNGP03.phx.gbl...
>|||Roy
Ok, so create a new role, add to the role users that you want to execute
sp. Go to the permission tab and click/check EXEC column for stored
procedure
Also , you can write script to grant an execute permission for all stored
procedure tospecific user. If you are interested I will post it out
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uW%23ObyaLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Shalom Uri
> This i've already know
> I'm looking for role for this and not go spesific on each store procedure.
> on the business i'm working on they are afraid to give the users
> db_owner's permission which i know that it is not a problem.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23XjUuaLHHA.1280@.TK2MSFTNGP04.phx.gbl...
>|||Roy
Be aware that if you have dymanic sql within a stored procedure you will
have to grant SELECT/UPDATE/INSERT/DELETE permission on underlaying table
as well
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u$6IH4aLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Roy
> Ok, so create a new role, add to the role users that you want to execute
> sp. Go to the permission tab and click/check EXEC column for stored
> procedure
> Also , you can write script to grant an execute permission for all stored
> procedure tospecific user. If you are interested I will post it out
>
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:uW%23ObyaLHHA.4244@.TK2MSFTNGP04.phx.gbl...
>|||Whell Uri
For this i have already gave db_datareader and db_datawriter role which
supply this need.
In fact before i deal there, it was as you said.
Now after i add db_datareader and db_datawriter this problem has gone and
there is full permission
it is seems to be some stupide of microsoft not to have role to execute
store procedures. it looks like the is reason for it. i'm wondering why
thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ed005aLHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Roy
> Be aware that if you have dymanic sql within a stored procedure you will
> have to grant SELECT/UPDATE/INSERT/DELETE permission on underlaying table
> as well
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u$6IH4aLHHA.2456@.TK2MSFTNGP06.phx.gbl...
>|||Roy Goldhammer (roy@.hotmail.com) writes:
> For this i have already gave db_datareader and db_datawriter role which
> supply this need.
> In fact before i deal there, it was as you said.
> Now after i add db_datareader and db_datawriter this problem has gone and
> there is full permission
> it is seems to be some stupide of microsoft not to have role to execute
> store procedures. it looks like the is reason for it. i'm wondering why
You can't do this in SQL 2000, as far as I know. But in SQL 2005,
permissions cascade, and you can say things like:
grant execute on database::yourdb to somerole
grant execute on schema::dbo to someotherrole
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

access link table

Hi,All
I created a view in our SQL server database, which in the view I can delete
a record according to the cascade delete relationship. Now, we would like to
do it in Access database, so I created an ODBC link to our access database,
from access database I tried to delete the record as well, I got the error
message:
ODBC-delete on a linked table 'dbo_vwSpecialConcerts' failed.
[Microsofe][ODBC SQL Server Drive][SQL Server]View or function
'dbo.vwSpecialConcerts' is not updatable because the modification affects
multiple base tables. (#4405).
Any ideas to solve the problem ?
It's hard to say without knowing what's in the view, but I suspect
you'll need to perform the update against the base table(s) or create
a view with no joins.
--Mary
On Mon, 14 Feb 2005 16:45:06 -0800, "Symphony"
<Symphony@.discussions.microsoft.com> wrote:

>Hi,All
>I created a view in our SQL server database, which in the view I can delete
>a record according to the cascade delete relationship. Now, we would like to
>do it in Access database, so I created an ODBC link to our access database,
>from access database I tried to delete the record as well, I got the error
>message:
>ODBC-delete on a linked table 'dbo_vwSpecialConcerts' failed.
>[Microsofe][ODBC SQL Server Drive][SQL Server]View or function
>'dbo.vwSpecialConcerts' is not updatable because the modification affects
>multiple base tables. (#4405).
>Any ideas to solve the problem ?

access link table

Hi,All
I created a view in our SQL server database, which in the view I can delete
a record according to the cascade delete relationship. Now, we would like to
do it in Access database, so I created an ODBC link to our access database,
from access database I tried to delete the record as well, I got the error
message:
ODBC-delete on a linked table 'dbo_vwSpecialConcerts' failed.
[Microsofe][ODBC SQL Server Drive][SQL Server]View or function
'dbo.vwSpecialConcerts' is not updatable because the modification affects
multiple base tables. (#4405).
Any ideas to solve the problem ?It's hard to say without knowing what's in the view, but I suspect
you'll need to perform the update against the base table(s) or create
a view with no joins.
--Mary
On Mon, 14 Feb 2005 16:45:06 -0800, "Symphony"
<Symphony@.discussions.microsoft.com> wrote:

>Hi,All
>I created a view in our SQL server database, which in the view I can delete
>a record according to the cascade delete relationship. Now, we would like t
o
>do it in Access database, so I created an ODBC link to our access database,
>from access database I tried to delete the record as well, I got the error
>message:
>ODBC-delete on a linked table 'dbo_vwSpecialConcerts' failed.
>[Microsofe][ODBC SQL Server Drive][SQL Server]View or function
>'dbo.vwSpecialConcerts' is not updatable because the modification affects
>multiple base tables. (#4405).
>Any ideas to solve the problem ?

Access is denied when executing xp_cmdshell delete

hi y'all!
here i am again with another problem:

declare @.path varchar (100)
declare @.filename varchar (100)
delcare @.delete_this varchar (100)
declare @.cmd varchar (100)

set @.path = '"\\servername\shared\"'
set @.filename = 'file.txt'
set @.delete_this = @.path+@.filename
set @.cmd = 'del '+@.delete_this

exec master..xp_cmdshell @.cmd

i get this:

Access is denied.

Please Advise
Thnx.I've also heard of "net use"

Can u please enlighten me on this.

Access Hangs on "Delete *" from linked table

I have a table called TKSD0UJA in my sqlserver that access links to.
The codes is:
strSel = "DELETE * FROM [TKSD0UJA];"
db.Execute strSel
it hands on db.Execute. I checked permissions within the sql server and
I have delete permission. Is there any reason why it should hang?Ari
Are you sure you don't need WHERE clause in the query?
How much does data exist in the table?
"Ari" <Kooshesh@.gmail.com> wrote in message
news:1130938827.948678.159380@.g44g2000cwa.googlegroups.com...
>I have a table called TKSD0UJA in my sqlserver that access links to.
> The codes is:
> strSel = "DELETE * FROM [TKSD0UJA];"
> db.Execute strSel
> it hands on db.Execute. I checked permissions within the sql server and
> I have delete permission. Is there any reason why it should hang?
>|||> The codes is:
> strSel = "DELETE * FROM [TKSD0UJA];"
You don't delete columns, you delete rows. So, the syntax "DELETE * FROM"
makes no sense. Try DELETE FROM tablename or DELETE tablename.
A|||much thanks