SQL Server 2005 "public" database role doesn't seem to apply?

Question

I have a SQL Server 2005 database that I'm trying to access as a limited user account, using Windows authentication. I've got BUILTIN\Users added as a database user (before I did so, I couldn't even open the database). I'm working under the assumption that everybody is supposed to have permissions for the "public" role applied to them, so I didn't do anything with role assignment. Under tblFoo, I can use the SSMS Properties dialog (Permissions page) to add "public", then set explicit permissions. Among these is "Grant" for SELECT. But running

SELECT * from tblFoo;

as a limited (BUILTIN\Users) account gives me an error "Select permission denied on object 'tblFoo', database 'bar', schema 'dbo'". In the properties dialog, there's an "Effective Permissions button, but it's greyed out.

Further, I tried creating a non-priv account called "UserTest", adding that at the server level, then mapping it down to the "bar" database. This let me add UserTest to the "Users or Roles" list, which let me run "Effective Permissions" for the account. No permissions are listed at all -- this doesn't seem right. The account must be in public, and public grants (among other things) Select on tblFoo, so why doesn't the UserTest account show an effective permission? I feel like I'm going a bit crazy here.

ASIDE: I am aware that many people don't like using the "public" role to set permissions. This is just my tinkering time; in final design I'm sure we'll have several flexible (custom) database roles. I'm just trying to figure out the behavior I'm seeing, so please no "don't do that!" answers.

UPDATE: Apparently I know just enough SQL Server to be a danger to myself and others. In setting permissions (as I said, "among others"), I had DENY CONTROL. When I set this permission, I think I tried to look up what it did, had a vague idea, and decided on DENY. I cannot currently recall why this seemed the thing to do, but it would appear that that was the reason I was getting permission failures. So I'm updating my question: can anyone explain the "CONTROL" permission, as it pertains to tables?

Answer 1

Assuming "UserTest" is a domain user account, connect as a member of the sysadmin role and run

EXEC MASTER.dbo.xp_logininfo 'Domain\UserTest', 'all'

(substituting your domain name for "Domain")

this will display the Windows groups etc. that the account is inheriting security permissions from and the level of access, e.g. you would expect to see something like:

account name     type  privilege mapped  login name      permission path
domain\usertest  user   user            domain\usertest BUILTIN\Users

This will help troubleshoot where the account is inheriting permissions from, e.g. which Windows groups it is part of that have permissions to the database. If this all looks OK then I would follow your own advice and not mess with the public role.

• Create a database role in your database
• Assign explicit permissions for that role
• Create a server login for your user account
• Open the server login, go to the User Mapping section, click on the database and select the database role you created

Answer 2

You only need to have SELECT rights. In raw SQL (see the "script" icon/button in your dialogue box), it's GRANT SELECT ON dbo.tblFoo to public. This is the only permission needed to view the data,

In this case, the error message explicitly mentions "deny". "DENY" is a right in itself, so it mentions it,

If you had no rights, you'd get the message (very approximately) "tblFoo does not exist or you do not have rights"

"DENY CONTROL" is mentioned here. In this case, you denied all rights to the public role.

The grantee effectively has all defined permissions on the securable