I'm trying to create a new DB user in
SQL Server 2008 R2 that can only select from one SQL View. But no matter what I try when I login as this new user I can select from any table. I ran this query:
select princ.name , princ.type_desc , perm.permission_name , perm.state_desc , perm.class_desc , object_name(perm.major_id) from sys.database_principals princ left join sys.database_permissions perm on perm.grantee_principal_id = princ.principal_id WHERE princ.name = 'public' AND object_name(perm.major_id) = 'User_Table'
And get these results:
name type_desc permission_name state_desc class_desc (No column name) public DATABASE_ROLE DELETE GRANT OBJECT_OR_COLUMN User_Table public DATABASE_ROLE INSERT GRANT OBJECT_OR_COLUMN User_Table public DATABASE_ROLE REFERENCES GRANT OBJECT_OR_COLUMN User_Table public DATABASE_ROLE SELECT GRANT OBJECT_OR_COLUMN User_Table public DATABASE_ROLE UPDATE GRANT OBJECT_OR_COLUMN User_Table
Does this mean no matter what anyone that logs in into the database will have full access based on the 'public' permissions?