i guess the question is how are you testing the public permissions then?

did you create a new database user , unconnected form any login like this?

are you using execute as? could the user you test against also have sysadmin login priviledges?

--create a test user in our specific database.

CREATE USER [ClarkKent] WITHOUT LOGIN--make sure I'm in the correct database

--test:

EXECUTE AS USER='ClarkKent'

--who am i?

select suser_name()

--what kind of permissions do i have?

--for 2008 and above:

--the auditing snippet below works fine in a

--login trigger,

--database trigger

--or any stored procedure.

SELECT

getdate() AS EventDate,

DB_NAME() AS DBName,

CURRENT_USER AS CurrentUser,

HOST_NAME() AS HostName,

APP_NAME() AS ApplicationName,

OBJECT_NAME(@@PROCID) AS ProcedureName,

USER_ID() AS Userid,

USER_NAME() AS UserName,

SUSER_ID() AS sUserid,

SUSER_SNAME() AS sUserName,

IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

IS_MEMBER('db_owner') AS [Is_DB_owner],

IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],

--these params were introduced in SQl2008

ConnectionProperty('net_transport') AS 'net_transport',

ConnectionProperty('protocol_type') AS 'protocol_type',

ConnectionProperty('auth_scheme') AS 'auth_scheme',

ConnectionProperty('local_net_address') AS 'local_net_address',

ConnectionProperty('local_tcp_port') AS 'local_tcp_port',

ConnectionProperty('client_net_address') AS 'client_net_address',

ConnectionProperty('physical_net_transport') AS 'physical_net_transport'

--this database permissions?

SELECT

*

FROM sys.database_permissions permz

INNER JOIN sys.database_principals userz

ON permz.grantee_principal_id = userz.principal_id

WHERE userz.principal_id IN( --the userid and also all the roles i'm directly in.

--that doesn't seem to get the role-within-a-role info those

SELECT

USER_ID() AS id

UNION ALL

SELECT

rolezx.role_principal_id

FROM sys.database_principals userzx

LEFT OUTER JOIN sys.database_role_members rolezx

ON userzx.principal_id = rolezx.member_principal_id

LEFT OUTER JOIN sys.database_principals decripz

ON rolezx.role_principal_id = decripz.principal_id

WHERE userzx.name = USER_NAME())

--do stuff

--change back into superman

REVERT;