View Only Role

• EjSQLme

  SSCrazy

  Points: 2172

  More actions

  May 13, 2011 at 11:40 am

  #250892

  Hi,

  I created a database role that has multiple views in it with read only access. The issue that I'm running into is that when testing the role after assigning it to a test user, the test user is able to output data on some view when doing a select statement. Below is how the Role was created and granted select access to the views.

  If we take the below views an example, the test user can return output data on view1 and view3 but not on View2. When doing select on view2 no data is returned and no error message. But if I do select on View2 using SA access, I get data returned.

  Any idea on what I need to check?

  CREATE ROLE [ViewOnly]

  GRANT SELECT ON View1 TO ViewOnlyRole

  GO

  GRANT SELECT ON View2 TO ViewOnlyRole

  GO

  GRANT SELECT ON View3 TO ViewOnlyRole

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 13, 2011 at 11:47 am

  #1324492

  is an error returned, or just no data?

  check the schema of the underlying objects inside the view;

  for example, lets say dbo.View1 & 3 are accessing items under the same schema, dbo, security shortucts and the underlying tables being selected from are assumed to be accessible.

  but if dbo.View2 is accessing another schema, say dev.Tablename isntead of dbo.tablename, or is trying to access another database, linked server, etc, I'd expect an error about no access.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!