Re: What exactly can PUBLIC ROLE do in SqlServer?

From: Kalen Delaney (kalen_please_reply_on_public_newsgroups_delaney@compuserve.com)
Date: 03/30/03 

From: "Kalen Delaney" <kalen_please_reply_on_public_newsgroups_delaney@compuserve.com>
Date: Sun, 30 Mar 2003 10:52:03 -0800

The message you get when a non-sa runs sp_addlogin (error 15247) is not the
same permission violation error that you would get if you really didn't have
permissions. You can look at the text of sp_addlogin and see that this error
is actually generated within the sp_addlogin procedure itself. It is
actually NOT the same 'permission denied' message that indicates a true
permission violation. That is error 229.

So sp_helprotect does correctly tell you that public has permission to run
sp_addlogin, but there is something inside the procedure that keeps a
non-admin user from completing the procedure. There is no way to tell what
procedures would have this further kind of restriction INSIDE their code
without looking at the code every single procedure.

If you absolutely must know exactly what a public member can do in Master,
and you want to go beyond what sp_helprotect tells you, you'll have to run
tests of your own. Otherwise, I would just assume that public can read most
of the metadata in master, but can't change anything. Really, non-admins
should not be doing anything in master anyway.

HTH 

--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kanthi" <kanthi_mathi@hotmail.com> wrote in message
news:028001c2f69f$8888e0e0$a301280a@phx.gbl...
> Thanks Kalen,
>
> sp_helprotect shows that PUBLIC has execute permission for
> sp_addlogin. But, when PUBLIC user executes sp_addlogin,
> it fails with 'Permission denied' as it doesn't have
> insert privileges on syslogins table. So, even though
> sp_helprotect shows that PUBLIC has execute privileges on
> sp_addlogin, in reality, it doesn't seem to be the case.
>
> So, my aim is to find out what exactly a PUBLIC user can
> do in Master database.
>
> Thanks for your help.
>
> >-----Original Message-----
> >Can you elaborate on exactly what you did to cause
> execution of the stored
> >procedures in master to fail?
> >
> >The permissions that the PUBLIC role has are exactly what
> you see when you
> >run sp_helprotect.
> >
> >If someone in the public role (meaning anybody or
> everybody) got an error
> >trying to execute something basic like sp_help, my guess
> is that you either
> >accidentally recreated sp_help with a different owner, or
> tried to have the
> >user execute the code that makes up sp_help, instead of
> directly executing
> >sp_help.
> >
> >If you tell us what you did exactly, we can tell you why
> you got the
> >behavior you saw.
> >
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Kanthi Mathi" <kanthi_mathi@hotmail.com> wrote in message
> >news:006901c2f5ce$0347cb20$3401280a@phx.gbl...
> >> Hi all,
> >>
> >> I need to know what exactly a PUBLIC ROLE is capable of
> >> doing in Master database. I issued sp_helprotect and
> >> gathered info about PUBLIC. Sp_helprotect does give the
> >> list of SPs that PUBLIC can execute, but when I test
> them,
> >> they fail because PUBLIC doesn't have the required
> >> permissions on their corresponding tables.
> >>
> >>
> >> Does anybody have the list of activities that PUBLIC is
> >> really capable of doing in Master database?
> >>
> >> I am looking for this info to define the security
> >> standards for SqlServer in our organisation.
> >>
> >> Thanks a lot for all your help.
> >>
> >
> >
> >.
> >

Relevant Pages

  • Re: System.Security.SecurityException was unhandled
    ... The exception gave you the CLSID. ... the first thing to check might be whether the COM server ... assembly actually has the permission in question. ... When I execute the application I received and error message. ...
    (microsoft.public.dotnet.security)
  • Re: System.Security.SecurityException was unhandled
    ... assembly actually has the permission in question. ... Try and find the sorce of the security permission error, ... setting was done on both versions 1.1 and 2.0 .NET framework. ... When I execute the application I received and error message. ...
    (microsoft.public.dotnet.security)
  • Re: System.Security.SecurityException was unhandled
    ... Is it a must that I need to register the COM server to the machine using ... CAS permissions on the client machine, and the COM issue is a new problem. ... assembly actually has the permission in question. ... When I execute the application I received and error message. ...
    (microsoft.public.dotnet.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)
  • Re: System.Security.SecurityException was unhandled
    ... I was using Oracle so was getting an Oracle permission problem. ... setting was done on both versions 1.1 and 2.0 .NET framework. ... On the server or the client? ... When I execute the application I received and error message. ...
    (microsoft.public.dotnet.security)