Re: Complete Neophyte Question(s)



James,

No you cannot remove a login from the 'public' role.

Yes, you can revoke, deny, or grant rights for the 'public' role to an
object such as dbo.Orders.

VIEW DEFINITION allows a user to _see_ the definitions of objects, such the
table structure, stored procedure code, etc., etc. I does NOT allow the
user to use the data in the table or view, does not allow the user to
execute the stored procedure, and so forth.

For rights to _use_ objects then the appropriate rights need to be granted
to an object & user/role pair. E.g.

GRANT SELECT ON dbo.Orders TO OrderViewers
GRANT EXECUTE ON dbo.UpdateOrders TO ServiceAgents

In general, for a database that is supposed to be secured it is a bad idea
to grant object level rights to the 'public' role for any object that is not
intended to be open to anyone who can connect to the database. For example,
should anyone who can use the database be able to change the data in
dbo.Order? If not, then you probably need to create a role for changing
Orders and other related tables, grant the need rights to that (or those)
roles, then REVOKE rights from public.

RLF

"James" <minorkeys@xxxxxxxxx> wrote in message
news:%234jCqpvwHHA.4140@xxxxxxxxxxxxxxxxxxxxxxx
47 rows. From what I can gather, it's impossible to remove a login from
the public role? I believe public has permissions defined on pretty much
all database objects. I'm not sure if that's how the default Northwind
database comes or if a previous person has mucked with it. Either way,
public has: Delete, Insert, References, Select and Update permissions on
dbo.Orders.

How does setting select access to tables, views, stored procs, etc.
ultimately differ from granting View Definition permissions? Just that
one is at the login level and one is at the role level?

Update: I have another database that's a demo database and it has Select
defined on sys.tables, yet the same login can't see tables in that
database.

I know I'm just missing one fundamental here. Thank you so much for your
help!

"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns99695F6CF7BYazorman@xxxxxxxxxxxx
James (minorkeys@xxxxxxxxx) writes:
That makes complete sense to me, but somehow I'm overlooking something.

I have a user named james on a database. If I go into properties for
that user they have no owned schemas. They have no role membership
(including public, which isn't listed here for some reason). If I
right-click the database and go to properties -> Permissions, the only
permission they have is Connect, not view definition.

The only thing I can see is that there's a login of the same name at the
server level which is a member of the Server Role public, but my
understanding is that it's unrelated.

So the long and the short of it, is that this user can view definition
on
this database, and I can't figure out why.

If you under database roles look at the public role, does it have any
permissions on anything?

If you run:

execute as login = 'james'
go
select * from sys.fn_my_permissions('dbo.Orders', 'object')
go
revert

What do you see?



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx




.