Re: Dts execution and the public role

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 09/10/04 

Date: Fri, 10 Sep 2004 06:08:07 +0100

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
> > >
> >
> >
> >

Relevant Pages

  • Re: Column Level Permissions Security Issue
    ... role is granted SELECT rights as in "GRANT SELECT TO RWE", then a DENY ... When working with security in SQL Server it's imperative to understand ... discusses ownership chaining: http://www.sommarskog.se/dynamic_sql.html. ... there are users who have permission to access this ...
    (microsoft.public.sqlserver.security)
  • Re: SQL CLR Sproc isnt running right
    ... did you grant rights using the SQL Server 2005 Surface Area Config tool? ... and I got a permissions error back saying DBO doesn't have permission to insert records into the table... ... but the assemblie's stored procedures do all have execute permissions set for the role that the executing user is in... ... "William Vaughn" wrote in message ...
    (microsoft.public.dotnet.framework.adonet)
  • After 2003 to 2005 Upgrade Wizard, Getting System.Security.SecurityException
    ... Crystal Report from a SQL Server 2000 table. ... Friend WithEvents scnSQLConnection As _ ... The dialog box suggests clicking on "Add Permission to the Project." ... Restart the debugging session, same problem again. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Error !!! SQL XML Bulkload Please help
    ... Irwin Dolobowsky ... > The schedule job gets run by SQL server Agent and the SQL server agent is ... > which has also got the administrator permission. ... Is the scheduled job running as a different user? ...
    (microsoft.public.sqlserver.xml)
  • System.Security.SecurityException with .NET 2005 Only
    ... Crystal Report from a SQL Server 2000 table. ... Friend WithEvents scnSQLConnection As _ ... The dialog box suggests clicking on "Add Permission to the Project." ... Restart the debugging session, same problem again. ...
    (microsoft.public.dotnet.languages.vb)