We have a set of Views that we only want a client to be able to see. The
client has been given a local, SQL Server login with sysadmin rights. A
corresponding User on the database has been granted SELECT permission against
the Views. The Views have been created with dbo as the schema owner.

We have discovered that the user can use Access to link to to the Server
using ODBC and not only see the Views, but all the underlying tables as well.

Since we only want the client to see the views, no matter how they connect,
and not be able to see any of the underlying tables, the Views need to be
dropped and recreated with a different schema owner, or is there another way
to restrict what objects can be seen?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...erver/200809/1

It seems that ODBC Login which you connected to the server has more
permission than just SELECT on view. Make sure that you connect via ODBC
with the same login (only SELECT perm on views)


"cbrichards via SQLMonster.com" <u3288@uwe> wrote in message
news:8a19b48522527@uwe...
> We have a set of Views that we only want a client to be able to see. The
> client has been given a local, SQL Server login with sysadmin rights. A
> corresponding User on the database has been granted SELECT permission
> against
> the Views. The Views have been created with dbo as the schema owner.
>
> We have discovered that the user can use Access to link to to the Server
> using ODBC and not only see the Views, but all the underlying tables as
> well.
>
> Since we only want the client to see the views, no matter how they
> connect,
> and not be able to see any of the underlying tables, the Views need to be
> dropped and recreated with a different schema owner, or is there another
> way
> to restrict what objects can be seen?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums...erver/200809/1
>

cbrichards via SQLMonster.com wrote:
> We have a set of Views that we only want a client to be able to see. The
> client has been given a local, SQL Server login with sysadmin rights. A
> corresponding User on the database has been granted SELECT permission against
> the Views. The Views have been created with dbo as the schema owner.
>
> We have discovered that the user can use Access to link to to the Server
> using ODBC and not only see the Views, but all the underlying tables as well.
>
> Since we only want the client to see the views, no matter how they connect,
> and not be able to see any of the underlying tables, the Views need to be
> dropped and recreated with a different schema owner, or is there another way
> to restrict what objects can be seen?
>

Since you have granted the login 'sysadmin' - that login has full access
to everything in SQL Server regardless of what you assign the user. In
fact, that login does not even need a user in any database to have full
access to the database.

What you need to do is remove the role - then, the database level
privileges will be honored and the user will only be able to select from
the views that you have granted access on.

Jeff