locked
Select permission on Views RRS feed

  • Question

  • Hi,

    I have created views under the Schema X which references tables under Schema Y.

    I have to give select permission only on the views.The owner of both the schemas are same.I have granted select permission on these views to a particular user.

    When i run the below query with the users login it throws error:The select permission was denied on the object 'XYZ',database 'XX',Schema 'Y'.(The view Test1 references table XYZ of schema Y)

    Select top 10 * from X.Test1

    But when i run below 2 queries together, it returns result.

    Select top 10 * from X.Test1

    Select top 10 * from X.Test2

    I am not able to figure out why is it behaving this way.Could you please help

    Thursday, January 24, 2013 2:40 PM

Answers

  • I have created views under the Schema X which references tables under Schema Y.

    I have to give select permission only on the views.The owner of both the schemas are same.I have granted select permission on these views to a particular user.

    When i run the below query with the users login it throws error:The select permission was denied on the object 'XYZ',database 'XX',Schema 'Y'.(The view Test1 references table XYZ of schema Y)

    Select top 10 * from X.Test1

    But when i run below 2 queries together, it returns result.

    Select top 10 * from X.Test1

    Select top 10 * from X.Test2

    Hi nvr,

    I just reproduced the scenario as yours. It is all OK.

    I created views Test1 under the Schema X which references tables under Schema Y.  Schema X and Schema Y have the same owner. I just grant Select the View Test1 permission to user A.

    When I execute as user=’A’, I could run command below successfully.
    Select top 10* from X. Test1

    You could try it again to test it.

    If you have any problem, please feel free to let me know.

    Thanks.


    Maggie Luo
    TechNet Community Support

    • Marked as answer by Maggie Luo Thursday, February 7, 2013 6:59 AM
    Monday, February 4, 2013 12:04 PM
  • Run the query below to list objects with an explicit owner at the object level that is different from the schema owner.  As Erland mentioned, an explicit owner at the object level will prevent the schema owner from being inherited and will break the ownership chain if different.

    SELECT
    	s.name AS SchemaName
    	,o.name AS ObjectName
    	,USER_NAME(s.principal_id) AS SchemaOwner
    	,USER_NAME(o.principal_id) AS ObjectOwner
    	,USER_NAME(COALESCE(o.principal_id, s.principal_id)) AS EffectiveOwner
    FROM sys.objects AS o
    JOIN sys.schemas AS s ON
    	s.schema_id = o.schema_id
    WHERE
    	s.principal_id <> o.principal_id;
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Maggie Luo Thursday, February 7, 2013 6:59 AM
    Monday, February 4, 2013 12:59 PM

All replies

  • Hi

    Off the top my experience has been that sql server doesn't let you have any permissions unless explicitly told so.

    I would look into Ownership Chains (link below) to see if the issue is related.

    http://msdn.microsoft.com/en-us/library/ms188676.aspx

    I hope this is on the right track until an MVP or MSFT Eng replies.

    Frank


    Frank Garcia

    Thursday, January 24, 2013 5:56 PM
  • Have you checked that the owners of the tables and the views are the same? While, by default, the owner of an object in a schema is the schema owner, this can be changed. As long as the column sys.objects.principal_id, the object owner is the same as the schema owner.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, January 24, 2013 10:37 PM
  • I have created views under the Schema X which references tables under Schema Y.

    I have to give select permission only on the views.The owner of both the schemas are same.I have granted select permission on these views to a particular user.

    When i run the below query with the users login it throws error:The select permission was denied on the object 'XYZ',database 'XX',Schema 'Y'.(The view Test1 references table XYZ of schema Y)

    Select top 10 * from X.Test1

    But when i run below 2 queries together, it returns result.

    Select top 10 * from X.Test1

    Select top 10 * from X.Test2

    Hi nvr,

    I just reproduced the scenario as yours. It is all OK.

    I created views Test1 under the Schema X which references tables under Schema Y.  Schema X and Schema Y have the same owner. I just grant Select the View Test1 permission to user A.

    When I execute as user=’A’, I could run command below successfully.
    Select top 10* from X. Test1

    You could try it again to test it.

    If you have any problem, please feel free to let me know.

    Thanks.


    Maggie Luo
    TechNet Community Support

    • Marked as answer by Maggie Luo Thursday, February 7, 2013 6:59 AM
    Monday, February 4, 2013 12:04 PM
  • Run the query below to list objects with an explicit owner at the object level that is different from the schema owner.  As Erland mentioned, an explicit owner at the object level will prevent the schema owner from being inherited and will break the ownership chain if different.

    SELECT
    	s.name AS SchemaName
    	,o.name AS ObjectName
    	,USER_NAME(s.principal_id) AS SchemaOwner
    	,USER_NAME(o.principal_id) AS ObjectOwner
    	,USER_NAME(COALESCE(o.principal_id, s.principal_id)) AS EffectiveOwner
    FROM sys.objects AS o
    JOIN sys.schemas AS s ON
    	s.schema_id = o.schema_id
    WHERE
    	s.principal_id <> o.principal_id;
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Maggie Luo Thursday, February 7, 2013 6:59 AM
    Monday, February 4, 2013 12:59 PM