Home
General - Do you know every object name should be owned by dbo?
  v2.0 Posted at 15/11/2019 8:58 AM by Tiago Araujo

​​The reason is that you avoid ownership chain problems. Where Mary owns an object, Fred can read the object and then he creates a proc and he gives permission to Tom to execute. But Tom cant because there is a product chain of ownership.​​​​

CREATE PROCEDURE [Adam Cogan].[Sales by Year]

@Beginning_Date DateTime,

@Ending_Date DateTime AS

SELECT Orders.ShippedDate

,Orders.OrderID

,"vwOrderSubTotals".Subtotal

,DATENAME(yy,ShippedDate) AS Year

FROM Orders

INNER JOIN "vwOrderSubTotals"

ON Orders.OrderID = "vwOrderSubTotals".OrderID

WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

Figure: Bad example​

CREATE PROCEDURE [dbo].[Sales by Year]

@Beginning_Date DateTime,

@Ending_Date DateTime AS

SELECT Orders.ShippedDate

,Orders.OrderID

,"vwOrderSubTotals".Subtotal

,DATENAME(yy,ShippedDate) AS Year

FROM Orders

INNER JOIN "vwOrderSubTotals"

ON Orders.OrderID = "vwOrderSubTotals".OrderID

WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

Figure: Good example​​​


Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: