Home
General - Do you know every object name should be owned by dbo?
  v4.0 Posted at 2/01/2020 9:29 AM by Christian Morford-Waite

​​​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 cannot 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 E​​​xample​​​


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: