Stored Procedure returns different results if executed from Trigger vs Manual

I have a Stored Procedure that populates a table with data from another table. When I use an Update Trigger to execute th开发者_开发问答e SP 3 records are inserted into the table.

If I execute the SP manually I get 6 records in the table.

 'SET ANSI_NULLS ON', 
 'SET QUOTED_IDENTIFIER ON', 
 'SET NOCOUNT ON' 

are the same in the SP and Trigger.

This is the SP:

 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 -- =============================================
 -- Author:     
 -- Create date: 
 -- Description:    
 -- =============================================
 ALTER PROCEDURE [dbo].[Residency_Date_Summary_Populate]
-- Add the parameters for the stored procedure here

 AS
 BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

 DECLARE @StartDate datetime;
 SET @StartDate = DateAdd(year, DateDiff(year, 0, GetDate()), 0);

 WITH Tally (N) AS 
 (SELECT TOP (12) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
 FROM master.sys.objects), CTE1 AS
 (SELECT c.First_Name,
   c.Last_Name,
   c.ID,
    r.Building,       
   r.Move_In_Date,
   r.Move_Out_Date,
   StartOfMonth = DateAdd(month, t1.N-1, @StartDate),
   EndOfMonth   = DateAdd(day, -1, DateAdd(month, t1.N, @StartDate)),
   MonthNbr = t1.N
 FROM CONTACTS c
   JOIN Residency_Dates r
     ON c.ID = r.ID_U       
   JOIN Tally t1
     ON t1.N between month(r.move_in_date) and month(coalesce( r.move_out_date,        getdate  ()))), CTE2 AS
  ( SELECT First_Name,
   Last_Name,
   Building,
   MonthNbr,
   ID
   ,StayForMonth = CASE WHEN Move_In_Date > StartOfMonth AND Move_out_Date <=      EndOfMonth 
    THEN DateDiff(day, Move_In_Date, Move_Out_Date)
         WHEN Move_In_Date > StartOfMonth 
    THEN DateDiff(day, Move_In_Date, EndOfMonth)
         WHEN Move_out_Date > EndOfMonth 
    THEN DateDiff(day, StartOfMonth, EndOfMonth) 
         WHEN Move_out_Date IS NULL AND month(StartOfMonth) = month(GetDate())
    THEN DateDiff(day, StartOfMonth, GetDate())
         ELSE DateDiff(day, StartOfMonth, COALESCE(Move_Out_Date, EndOfMonth)) 
    END    
 FROM CTE1)
 INSERT into Residency_Date_Summary

 SELECT First_Name,
   Last_Name,
   Building,
   January = MAX(CASE WHEN MonthNbr = 1 THEN StayForMonth ELSE 0 END),
   February = MAX(CASE WHEN MonthNbr = 2 THEN StayForMonth ELSE 0 END),
   March = MAX(CASE WHEN MonthNbr = 3 THEN StayForMonth ELSE 0 END),
   April = MAX(CASE WHEN MonthNbr = 4 THEN StayForMonth ELSE 0 END),
   May = MAX(CASE WHEN MonthNbr = 5 THEN StayForMonth ELSE 0 END),
   June = MAX(CASE WHEN MonthNbr = 6 THEN StayForMonth ELSE 0 END),
   July = MAX(CASE WHEN MonthNbr = 7 THEN StayForMonth ELSE 0 END),
   August = MAX(CASE WHEN MonthNbr = 8 THEN StayForMonth ELSE 0 END),
   September = MAX(CASE WHEN MonthNbr = 9 THEN StayForMonth ELSE 0 END),
   October = MAX(CASE WHEN MonthNbr = 10 THEN StayForMonth ELSE 0 END),
   November = MAX(CASE WHEN MonthNbr = 11 THEN StayForMonth ELSE 0 END),
   December = MAX(CASE WHEN MonthNbr = 12 THEN StayForMonth ELSE 0 END)
 FROM CTE2  
 GROUP BY First_Name, Last_Name, Building
 ORDER BY Last_Name, First_Name, Building;

End

The problem is the code you are using to generate a tally table of numbers

WITH Tally (N) AS 
(SELECT TOP (12) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM master.sys.objects)

When I log in as a user with limited permissions select * FROM master.sys.objects returns only 6 items compared to 82 when logged in as sa. As a result your tally table would only have 6 items not the 12 that you expected when run under certain logins.

Your "fix" of adding the login to the sysadmin group is, frankly, ridiculous. You will still get the problem if another login fires the trigger and if your application has any SQL injection vulnerabilities giving the login sysadmin rights will allow the attacker to do pretty much anything.

All you need to do is replace

WITH Tally (N) AS 
 (SELECT TOP (12) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
 FROM master.sys.objects), CTE1 AS
 (SELECT c.First_Name,
   c.Last_Name,

with

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
     E02(N) AS (SELECT 1 FROM E00 a, E00 b),
     E04(N) AS (SELECT 1 FROM E02 a, E02 b),
     Tally (N) AS (SELECT TOP (12) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM E04), 
     CTE1 AS
 (SELECT c.First_Name,
   c.Last_Name,