September 21, 2010 at 11:25 am
Using Sql Server 2005, I have a View, a Schema, a local SQL account and a domain account. I have a requirement for a View(s) to be accessed remotely over ODBC and I want the users to login using their domain accounts, but to only be able to see and run the View(s) within the schema.
I created a schema called GSOC and placed the View within it (CREATE VIEW [GSOC].[VIEWNAMEHERE]). I then created a local SQL Login account with GSOC as the default schema, added it to the GSOC schema and granted it SELECT permissions. Using Excel / ODBC I was able to connect to the SQL database and had access to only the View in the GSOC schema, which is exactly what I wanted.
So then I added a SQL Login using a Windows domain account. Then I added the domain account to the GSOC schema granting SELECT permissions, but when my user (who had no other access to the database) logs in over ODBC he can see ALL the Views and Tables for that database.
What am I missing about the domain account that allows it to see everything vs a local SQL account that is more restrictive?
September 28, 2010 at 12:40 pm
Was the mapping of the domain account set up with a Default Schema?
If the domain account is a group, you probably can't set it up that way. If it's an individual user domain account, set it up with the default schema of your new schema. Otherwise, by default the default is dbo.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply