Re: Complete Neophyte Question(s)


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.


"James" <minorkeys@xxxxxxxxx> wrote in message
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

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

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

"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
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
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'
select * from sys.fn_my_permissions('dbo.Orders', 'object')

What do you see?

Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at


Relevant Pages

  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
  • Re: Simple SQL Security Question From SQL Newbie
    ... >exec sp_grantdbaccess 'WIN2K\ASPNET' ... >VS.NET, I can use the server explorer and I can see the database, it's ... >login 'WebCalendar'. ... I also did a GRANT ALL on y2003 TO ASPNET ...
  • Re: SQL Server 2005 Stored Procedure security annoyances
    ... stored proecedure on a database wide level? ... GRANT EXECUTE ON SCHEMA::MySchema TO MyRole ... I am sure I will forget some SP's and probably forget to set the rights ... permissions because not all stored procedures are equal. ...
  • Re: GRANT Select to all tables on a DB
    ... solution that Geoff provided is a quick and easy way to provide select ... rights on all tables to a specific database user. ... Now you say that you want to grant or deny. ...
  • Re: Removing Permissions from the public role
    ... did you think about putting those 8 tables in a different schema, and then just granting rights to that schema, revoking the rights for that group from public. ... that user and ran some grant scripts to allow them to select from the 8 ... I see that there is a public role ... the public security role is still allowing the new login access to all ...