Tuesday, February 14, 2012

can not view DTS Packages and Jobs

I have SQL 2k SP4 on Windows 2003
Can A sqlserver user, with out having SA role, see the DTS packages, Job and
their status ?
--
RKYes, partially, through some workarounds.
If you make a SQL Server user a member of the msdb database
TargetServersRole, they will be able to see all jobs and their statuses, but
will not be able to create or modify jobs. (So this does not work for
someone who should be able to create his own jobs. This is an undocumented
sideeffect of the role and will not work in SQL Server 2005. But 2005 has
specific new roles for granting various degrees of access to SQL Agent
jobs.)
If, instead of storing DTS packages as SQL Server objects, you store them as
files on a file share, then anyone who has rights to the file share
(read/only if you want that) can examine the contents of the DTS package, as
well. (If there is a workaround for examining DTS packages stored on the
server, I don't know it.)
RLF
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>I have SQL 2k SP4 on Windows 2003
> Can A sqlserver user, with out having SA role, see the DTS packages, Job
> and
> their status ?
> --
> RK|||Thank you Russel
I stored the DTS as stuctures storage file. What application/viewer do you
use the examine the contents ?
--
RK
"Russell Fields" wrote:
> Yes, partially, through some workarounds.
> If you make a SQL Server user a member of the msdb database
> TargetServersRole, they will be able to see all jobs and their statuses, but
> will not be able to create or modify jobs. (So this does not work for
> someone who should be able to create his own jobs. This is an undocumented
> sideeffect of the role and will not work in SQL Server 2005. But 2005 has
> specific new roles for granting various degrees of access to SQL Agent
> jobs.)
> If, instead of storing DTS packages as SQL Server objects, you store them as
> files on a file share, then anyone who has rights to the file share
> (read/only if you want that) can examine the contents of the DTS package, as
> well. (If there is a workaround for examining DTS packages stored on the
> server, I don't know it.)
> RLF
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
> >I have SQL 2k SP4 on Windows 2003
> > Can A sqlserver user, with out having SA role, see the DTS packages, Job
> > and
> > their status ?
> > --
> > RK
>
>|||RK,
From Enterprise Manager you right-click on Data Transformation Services and
choose Open Package. It will then open a package from a structured storage
file into the DTS editor.
RLF
"RK73" <RK@.discussions.microsoft.com> wrote in message
news:7B708C46-D5CB-46B3-BCA8-9A91FA26C035@.microsoft.com...
> Thank you Russel
> I stored the DTS as stuctures storage file. What application/viewer do you
> use the examine the contents ?
> --
> RK
>
> "Russell Fields" wrote:
>> Yes, partially, through some workarounds.
>> If you make a SQL Server user a member of the msdb database
>> TargetServersRole, they will be able to see all jobs and their statuses,
>> but
>> will not be able to create or modify jobs. (So this does not work for
>> someone who should be able to create his own jobs. This is an
>> undocumented
>> sideeffect of the role and will not work in SQL Server 2005. But 2005
>> has
>> specific new roles for granting various degrees of access to SQL Agent
>> jobs.)
>> If, instead of storing DTS packages as SQL Server objects, you store them
>> as
>> files on a file share, then anyone who has rights to the file share
>> (read/only if you want that) can examine the contents of the DTS package,
>> as
>> well. (If there is a workaround for examining DTS packages stored on the
>> server, I don't know it.)
>> RLF
>> "RK73" <RK73@.discussions.microsoft.com> wrote in message
>> news:4401345C-6ABC-44EA-9756-EDD1133CDCE1@.microsoft.com...
>> >I have SQL 2k SP4 on Windows 2003
>> > Can A sqlserver user, with out having SA role, see the DTS packages,
>> > Job
>> > and
>> > their status ?
>> > --
>> > RK
>>

No comments:

Post a Comment