I have a database with schema foo with table test
i have a schema bar with view vw_test
create view bar.vw_test
select x,y,z from foo.test
etc etc etc
now i add a user to the server and database and just have grant select on schema::bar to user permissions
Now, if the behaviour was consistent between the database environments I'd know where to begin (all running sql server r2 (RTM) btw)
I add a sql service account to one environment and grant select on schema, works fine. I add an AD account, works fine
I try the same on another environment and it doesn't work unless i do something like
select * from bar.[vw_test];
Im not joking. select * from bar.vw_test wont work, neither will select * from bar.[vw_test]
it needs to have a ; or go after it
The error is
The SELECT permission was denied on the object 'test', database 'xx', schema 'foo'.
Is there some database or server level option that's inconsistent across the environments? I ran the schema compare is VS2010 but it didn't pick anything up.
If i change it to quoted_identifiers off then it's the opposite. It only works if the command is by itself- cant have 'go' or ; after it.
My expectation was that i wouldnt need to grant explicit permissions to the und
View Complete Post