Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Permission Error on Trigger Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 1:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 12:11 PM
Points: 6, Visits: 18
I have a trigger that emails the user whenever their order has been marked as complete. This works fine.

Now, I'm working on a different application that modifies the table on which the trigger is set. The new application will never change the production status of the order, so the trigger isn't relevant. However, when the new application attempts to modify the table, an error occurs because the users of the new application don't have access to the database where the trigger acquires the user's email address.

I thought I had successfully coded the trigger in such a way that it would exit the trigger if the ProductionStatus bit field isn't set to 1, but I still receive the error message when modifying the table with the new application. Is there a way to avoid this error or will it still check permissions on the table even when that line of code isn't going to run?

USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[utr_ProductionStatusNotify] ON [dbo].[ProductionStatus]
FOR UPDATE
AS
BEGIN
DECLARE @SUBJECT as NVarChar (100)
DECLARE @BODY as NVarChar (2000)
DECLARE @ESTIMATOR As NVarChar(50)
DECLARE @ESTIMATOREMAIL As NVarChar(100)
DECLARE @ORDERID As NVarChar(50)
DECLARE @TO As NVarChar (100)
DECLARE @FROM As NVarChar (100)
DECLARE @CUSTOMERID AS NVARCHAR(100)
DECLARE @CUSTOMERNAME AS NVARCHAR(100)
DECLARE @JOBNAME AS NVARCHAR(100)

IF (SELECT ISNULL(i.ProductionComplete, d.ProductionComplete) FROM Inserted i
FULL JOIN Deleted d on i.OrderID = d.OrderID
WHERE ((IsNull(i.ProductionComplete, '') <> IsNull(d.ProductionComplete, '')))) = 0
BEGIN
RETURN
END
ELSE

SELECT @ORDERID = ISNULL(i.OrderID, d.OrderID) FROM Inserted i
FULL JOIN Deleted d on i.OrderID = d.OrderID
WHERE ((IsNull(i.ProductionComplete, '') <> IsNull(d.ProductionComplete, '')))

SELECT @ESTIMATOR = Estimator FROM Orders_Open WHERE OrderID = @ORDERID
SELECT @ESTIMATOREMAIL = Email FROM [Manko3].[dbo].[Users] WHERE Login = @Estimator
SELECT @CUSTOMERID = CustomerID FROM Orders_Open WHERE ORDERID = @ORDERID
SELECT @CUSTOMERNAME = Customer_Name FROM [Manko3].[dbo].[Customer_Information] WHERE Customer_ID = @CUSTOMERID
SELECT @JOBNAME = JobName FROM Orders_Open WHERE OrderID = @ORDERID
SELECT @SUBJECT = 'Order #' + @ORDERID + ' has been marked as Production Complete'
SELECT @BODY = 'Production has been completed on order #' + @ORDERID +', the ' + @JOBNAME + ' job for ' + @CUSTOMERNAME + '.'
SELECT @TO = @ESTIMATOREMAIL
SELECT @FROM = @FROMEMAIL

EXEC [dbo].[sp_SendEmail] @FROM, @TO, @SUBJECT, @BODY
END

Post #1472744
Posted Thursday, July 11, 2013 2:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:56 PM
Points: 13,081, Visits: 12,545
Your trigger will still run under the context of the current user, however you can execute it as another login by using the EXECUTE AS option.

Something like this:

ALTER TRIGGER [dbo].[utr_ProductionStatusNotify] ON [dbo].[ProductionStatus]
WITH EXECUTE AS UserWithAppropriatePermission
FOR UPDATE
AS
BEGIN
...



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1472768
Posted Thursday, July 11, 2013 2:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 12:11 PM
Points: 6, Visits: 18
But shouldn't the trigger exit prior to getting to the part where the user doesn't have permissions? Or does it automatically check for permissions regardless of whether the code is ever executed?
Post #1472771
Posted Thursday, July 11, 2013 2:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:56 PM
Points: 13,081, Visits: 12,545
skempf (7/11/2013)
But shouldn't the trigger exit prior to getting to the part where the user doesn't have permissions? Or does it automatically check for permissions regardless of whether the code is ever executed?


It won't just exit at some point. The reason you would use EXECUTE AS is so that when the trigger runs, it runs as though it is the user with elevated permissions instead of the current user that doesn't have access to that table. This allows the user to execute the code without the need to give them permission.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1472772
Posted Thursday, July 11, 2013 2:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 12:11 PM
Points: 6, Visits: 18
What is the point of the RETURN call in the trigger, if it doesn't cause the trigger to end execution?
Post #1472775
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse