question

Hi! I have just came across a strange issue with linked servers and permissions. In Server A, DB1, I have the following objects: - SOME_SCHEMA.tbl (a table) - SOME_SCHEMA.v_tbl (a view selecting some columns and rows from SOME_SCHEMA.tbl) - OTHER_SCHEMA.tbl (a table) - OTHER_SCHEMA.v_tbl (a view selecting some columns and rows from OTHER_SCHEMA.tbl) In server B, there's a linked server connection to server A. In DB1, there's a database role with SELECT permissions on the two views above, but no permissions on the underlying tables. The database role as one user as member, which is linked to a login for an Active Directory group. Members of the Active Directory group can login to Server A, and select from both views. But when logging into server B, the users can only use the first view. When selecting from the second view, they get the error message: ”The SCHEMA LOCK permission was denied on object ’tbl’, database ’db1’, schema ’OTHER_SCHEMA’”. My workaround for the moment has been to grant SELECT permissions on OTHER_SCHEMA.tbl to the database role. It works, but it fails my goal to restrict permission to certain columns and rows. Has anyone seen the same scenario? Are there other ways to solve the problem, without extending permissions too much?