SQL Server 2005 Schema Ownership & Domain Accounts

Question

Greetings all,

In SQL Svr 2005 I created a new schema for my database called GSOC whose owner is dbo. I then created a local SQL user account with the default schema set to GSOC - my new schema name. I have one View that I want this schema to control access to, so I added the local SQL account to the View's permissions granting SELECT permission only. The purpose of this is to secure the View so that a user can authenticate to SQL Server via ODBC and only the new View - nothing else - which is exactly what happens using the local SQL account.

The problem is that I want to do the same thing but with a domain account, so I added the domain account to SQL Server with it's default schema set to GSOC. And then added the domain account to the View's permissions, but the user tells me that he can see all Views and Tables - something we do not want when rolling out to production.

Any thoughts on what I'm overlooking?

Answer 1

A user/login can see any object they have permissions on.

If you want them to see only the view, they need permissions on only the view or the schema (which means all objects in the schema).

If they see all objects, it means they have db_owner rights. If it is all tables and views only, then it could be db_datareader.

FIY, Windows accounts/groups do not have default schemas.