Hide all system views/tables from users in SQL server 2005

  • martin-k

    SSC Rookie

    Points: 43

    Hello,

    I had the same problems with SQL Server 2008 R2 with ODBC. When connecting to any database all systemviews from db master were shown in addition.

    I have done following in the management studio: After selecting db master, properties, I added the role public in rights and revoked "select". That was all!

    Did you try this already?

    In ODBC-Management "Sql Server native client 10"-driver is a must! With older versions it does not work.

    New ODBC-connections can only be created with accounts without "public"-membership!

    best regards

    Martin

  • Mile Higher Than Sea Level

    SSCarpal Tunnel

    Points: 4828

    Image of what the discussion is about.

    Steps to limit a user to choose only the Views they have permission for:

    1. In Databases, Security, Logins - New Logins

    Login Name: GISviewer (password) turn off password enforce policy

    Default DB - RegDB User Mapping - RegDB

    2. run tsql on the view GISWell

    Use RegDB

    GRANT SELECT ON vGISWell TO [GISviewer]

    3 Open Access - External Data (Native SQL - add server name)

    in data source UserName GISViewer Password: .....

    Link Tables Result:

    Only the dbo.vGISWell shows on top ' desired!

    Plus.... Not Desired

    All the Information_Schema.check_constraints

    All of the sys.all_xxxx See link image above

    Did the solution above Work?

  • Mile Higher Than Sea Level

    SSCarpal Tunnel

    Points: 4828

    http://support.microsoft.com/kb/2513216

    Got to love Microsoft SQL Server Support

    After dozens of people asking how to solve this - Microsoft referenced this article.

    It sure shows the problem. But, it is the End User (all of them) we shoud educate?

    Microsoft warns that deny select to public may have unintended effects - but MS offers no real solution.

    Keywords: deny view definition to public

    Keywords: deny select to public

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply