Locking down permissions at the object level
Posted on 2010-11-30
I've been on an Oracle DB project for awhile, and i've gotten a little rusty on SQL Server. I have a user who wants to consume an existing SQL Server 2008 database in his BI application. So, i've creating a login for him for ODBC access.
I would like to make it so he can only see specific Views and/or Synonyms created just for his use, and nothing else. What should I configure to accomplish this? Would a schema work best, and if so how do I assign the objects to his schema?
I've tried giving him the deny data reader/writer permission and then granting him specific object permissions on the views/synonyms, but instead of seeing those specific objects all he can see is the information_schema.* and sys.* objects. Do I need to specifically deny permission to these?
Thoughts and/or best practices?