I'm trying to get a question answered and have not found the complete answer; consider the following:
I create ba new database, NewDB with a table, NewTable. I create a new login, let's make it an SQL login: NewUser. I give NewUser access to NewDatabase but assign NO PERMISSIONS what-so-ever. When I open a Querywindow on NewDB and change connection to NewUser; next I type SELECT * FROM NewTable and lo! I see a resultset! Which surprises me. I thought that the PUBLIC role default had not any permission, but it seems it has at least READ permission. Which is not what I would want.
Now, is it so, that a user which is given access to a database will always have read permission because of the PUBLIC role? I have checked the security catalogues for PUBLIC and find among other the following:
select * from sys.server_permissions where grantee_principal_id= (select principal_id from sys.server_principals where name='public')
The first result is this:
class class_descr major_id minor_d grantee_id grontor_id type permission_name state state_descr
100 SERVER 0 0 2 1 VWDB VIEW ANY DATABASEGGRANT
Specifically the type/permissionname makes me wonder: VWDB/View Any Database. This seems to be the default for the Public role, and it is the case on any SQL Server I have checked sofar, 2005 AND 2008.
Is this behaviour intentional? Do I miss something here?
Greetz,
Hans Brouwer