Permission confusion

It seems by default the public role is granted very generous permissions.
When I tried DENY ALL TO public is didn't seem to have any affect. I know it
will work by listing the objects or going into enterprise manager and
denying.
I am tempted to pull all permissions from public and then only allowing
absolutely needed permissions (although I don't know what those are yet).
Is there a reason for the public role to have so much power?
Is it better to make a new role and just add the guest and other accounts to
that role to limit their access instead of removing permissions from the
public role?DAC,
The Public database role doesn't have any "generous permissions". That
being said - are you refering to Pubs or Northwind? Additional permissions
are granted in these databases to make it easier for developers to learn
T-SQL. Also, if you're refering to a user-defined database that has
"generous permissions" you might want to take a look that the permissions
granted in the Model system database as all new databases are based off of
the Model system database.
HTH
Jerry
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:%23MAJ5ARxFHA.3300@.TK2MSFTNGP09.phx.gbl...
> It seems by default the public role is granted very generous permissions.
> When I tried DENY ALL TO public is didn't seem to have any affect. I know
> it will work by listing the objects or going into enterprise manager and
> denying.
> I am tempted to pull all permissions from public and then only allowing
> absolutely needed permissions (although I don't know what those are yet).
> Is there a reason for the public role to have so much power?
> Is it better to make a new role and just add the guest and other accounts
> to that role to limit their access instead of removing permissions from
> the public role?
>|||Thank you. The model database currently allows things like sysusers,
sysfiles, syscolumns, sysindexes and some procedures that I have not learned
about yet. Is this common or is it prudent to remove these permissions? Some
of what they reveal seems like giving away a lot of information, user names,
tables, columns, etc.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OG00dERxFHA.2880@.TK2MSFTNGP12.phx.gbl...
> DAC,
> The Public database role doesn't have any "generous permissions". That
> being said - are you refering to Pubs or Northwind? Additional
> permissions are granted in these databases to make it easier for
> developers to learn T-SQL. Also, if you're refering to a user-defined
> database that has "generous permissions" you might want to take a look
> that the permissions granted in the Model system database as all new
> databases are based off of the Model system database.
> HTH
> Jerry
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:%23MAJ5ARxFHA.3300@.TK2MSFTNGP09.phx.gbl...
>|||DAC,
I looked at my Model system database and there are permissions granted to
the public database role for may system tables/view but no stored procedures
so I'm not sure what you're seeing there. Normal? Yeah...and often time
required to allow certain sp_ functionality in SQL Server.
HTH
Jerry
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:u2HHJmRxFHA.2728@.TK2MSFTNGP14.phx.gbl...
> Thank you. The model database currently allows things like sysusers,
> sysfiles, syscolumns, sysindexes and some procedures that I have not
> learned about yet. Is this common or is it prudent to remove these
> permissions? Some of what they reveal seems like giving away a lot of
> information, user names, tables, columns, etc.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OG00dERxFHA.2880@.TK2MSFTNGP12.phx.gbl...
>|||Ok, thank you. It does help.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%235EulvRxFHA.1456@.TK2MSFTNGP11.phx.gbl...
> DAC,
> I looked at my Model system database and there are permissions granted to
> the public database role for may system tables/view but no stored
> procedures so I'm not sure what you're seeing there. Normal? Yeah...and
> often time required to allow certain sp_ functionality in SQL Server.
> HTH
> Jerry
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:u2HHJmRxFHA.2728@.TK2MSFTNGP14.phx.gbl...
>