Re: Dts execution and the public role
From: Claudia Holzer (ClaudiaHolzer_at_discussions.microsoft.com)
Date: 09/10/04
- Next message: Hemlata Shah: "Urgnt"
- Previous message: Beema: "Re: Transform data from a temp table"
- In reply to: Allan Mitchell: "Re: Dts execution and the public role"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 10 Sep 2004 06:09:03 -0700
Thank you Allan, I put in blank this pivileges for the public role of the
msdb DB, and then I put the specific privileges over the SP and the table for
the particular user, and then it works (If the user doesn't have the specific
privileges, can't execute the DTS, in that way I don't have a security
problem)
Thank you very much, seems so easy but I didn't prove this combination,
because I was worry about the security of the system
"Allan Mitchell" wrote:
> A DENY overrules the allow.
>
> You should REVOKE.
>
> In your permissions screen in EM the Exec checkbox for this SP should be
> blank NOT a red X
>
> From BOL
>
> "
> When a permission is denied from a SQL Server user or Windows NT user
> account, the specified security_account is the only account affected by the
> permission. If a permission is denied from a SQL Server role or a Windows NT
> group, the permission affects all users in the current database who are
> members of the group or role, regardless of the permissions that have been
> granted to the members of the group or role. If there are permission
> conflicts between a group or role and its members, the most restrictive
> permission (DENY) takes precedence. "
>
>
>
>
>
>
> --
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>
>
> "Claudia Holzer" <ClaudiaHolzer@discussions.microsoft.com> wrote in message
> news:8C7855A9-E5F2-446F-9D13-79DE51749EF2@microsoft.com...
> > Yes, that is not the problem. The real problem in the public role. My
> steps
> > are:
> > 1.- I create the new user
> > 2.- I put in some user database
> > 3.- I assign permissions in the msdb DB to this user, and specific in the
> SP
> > 'sp_get_dtspackage', and sysdtspackages table
> > 4.- I denied the permissions in the public role of the msdb DB (because
> this
> > is a best practice)
> > 5.- I assign to the user to a new role with the permissions too
> > 6.- I obtain the error when I try to run the DTS in my applications
> >
> > The only way that the error doesn't appear is when I take out the deny in
> > the public role of the msdb DB.
> >
> > I think that if I can delete the link between my user and the publis role
> I
> > resolve the problem, but in which way I can do that?.
> > Thanks
> >
> > "Allan Mitchell" wrote:
> >
> > > What is it you do not like about this?
> > >
> > > The reason any man and hos dog can execute this SP is because guest is a
> > > member of the MSDB database by default Why not remove Guest and add
> users
> > > specifically
> > >
> > > --
> > > --
> > >
> > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > www.SQLDTS.com - The site for all your DTS needs.
> > > www.konesans.com - Consultancy from the people who know
> > >
> > >
> > > "Claudia Holzer" <ClaudiaHolzer@discussions.microsoft.com> wrote in
> message
> > > news:E33B6B12-51A6-4AD7-B684-741F9FDF2B0C@microsoft.com...
> > > > I have some application in Visual Basic 6, and I create a DTS in my
> local
> > > > machine (my local SQl Server), and when I try to execute the DTS from
> the
> > > > application I have no problems , but when I put this DTs in some
> remote
> > > SQL
> > > > server, and then I try to execute my application, I obtain this
> message:
> > > > EXECUTE permission denied on object 'sp_get_dtspackage', database
> msdb,
> > > > owner 'dbo'
> > > > I when I review I can see that the problem is because I denied the
> > > > privileged for this SP to a public role (if I simple take out the
> denied I
> > > > have no problem).
> > > > I don't like to take out this 'denied' to the public role, because it
> can
> > > be
> > > > dangerous, I like to know if I have another way to resolve this.
> Thanks
> > > >
> > >
> > >
> > >
>
>
>
- Next message: Hemlata Shah: "Urgnt"
- Previous message: Beema: "Re: Transform data from a temp table"
- In reply to: Allan Mitchell: "Re: Dts execution and the public role"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
