• this can be a little misleading, especially when it comes ot using sp_helpprotect.

    that procedure lists the objects that the public role,(in this specific example) can see,

    if you look at the list,the OWNER column specifically, it's all information_schema and sys, and lots of objects which belong to those schemas; that is PERFECTLY NORMAL.

    an end user has access to the view sys.tables for example, but he can see rows INSIDE that view he actually has access to...so if you create a new table, or for any existing tables, a person that exists only in the public role does not know that those tables exist.

    the public role gives end users the ability to find data that they have access to...and the data in the sys views are filtered by end user permissions.

    test this for yourself: create a user, without any other roles, so it only has the default public permissions.

    CREATE USER TestUser WITHOUT LOGIN;

    EXECUTE AS User='TestUser'

    select * from sys.tables --i only see some spt_* tables, but I KNOW there are other tables there!

    REVERT; --change back into my original role

    DROP USER TestUser

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!