Re: Dts execution and the public role

#1

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 (AT) discussions (DOT) microsoft.com> wrote

Quote:

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

#2

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 (AT) discussions (DOT) microsoft.com> wrote

Quote:

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 (AT) discussions (DOT) microsoft.com> wrote in
message
news:E33B6B12-51A6-4AD7-B684-741F9FDF2B0C (AT) microsoft (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Dts execution and the public role

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Dts execution and the public role - 09-09-2004 , 02:36 PM

Re: Dts execution and the public role - 09-10-2004 , 12:08 AM