Permission Issues running DTS from Agent

We have a DTS which loads files using FTP to another
server. This will be used by app groups and will be run
via click of button using sp_start_job,

Somehow the userid running the job gets "not sysadmin to
run cmdshell.." Top fix that, we granted it to execute the
xp_cmdshell and also created a proxy account for SQL Agent
which is an admin on server and sql. However, still we get
permission errors like "...The needed permission is
missing to run command shell.." Please help . The DTS is
being called from SQl Agent job and we don't want an admin
ID to be used .Do you have 'exec master.dbo.' in from of xp_command... :confused:

Permission

Hi,
If I grant a user with the permission of db_datareader in EM, do I also
need to click on the Permission Button and check on every table/view in orde
r
for her to view them or the db_datareader means a user can view all
table/view by default?
Thanks
EdEd,
No. As BOL says "Can select all data from any user table in the database.".
Here is a script to prove it, assuming that your server has "SQL Server and
Windows" authentication.
Example:
-- DO NOT TEST IT IN A PRODUCTION SERVER
use master
go
execute sp_addlogin 'albert', 'b1r12-36', 'northwind'
go
use northwind
go
execute sp_grantdbaccess 'albert', 'albert'
go
execute sp_addrolemember 'db_datareader', 'albert'
go
select
a.*
from
openrowset('sqloledb', 'server=yourServer;uid=albert;pwd=b1r12-36',
northwind.dbo.orders) as a
go
execute sp_droprolemember 'db_datareader', 'albert'
go
execute sp_revokedbaccess @.name_in_db = 'albert'
go
use master
go
declare @.spid int
declare @.s varchar(50)
select
@.spid = spid
from
sysprocesses
where
loginame = 'albert'
and dbid = db_id('northwind')
if @.spid is not null
begin
set @.s = 'kill ' + ltrim(@.spid)
exec(@.s)
end
go
execute sp_droplogin @.loginame = 'albert'
go
AMB
"Ed" wrote:

> Hi,
> If I grant a user with the permission of db_datareader in EM, do I also
> need to click on the Permission Button and check on every table/view in or
der
> for her to view them or the db_datareader means a user can view all
> table/view by default?
> Thanks
> Ed