:::: MENU ::::

Posts Categorized / Triggers

  • Aug 21 / 2013
  • 0
dbDigger, DDL, DDL Triggers, SQL Server Error messages, SQL Server Integration Services (SSIS), Triggers

Error generated in SSIS task

While importing few tables from server instance to another i got following error message.

http://4.bp.blogspot.com/-SQO5uAIPeh8/UhTljUKxEWI/AAAAAAAADR4/Xm_GZfJcKfQ/s1600/Problem+with+the+query+Result+Set+property+not+set+correctly,+parameters+not+set+correctly+or+connection+not+established+correctly

The user does not have permission to perform this action. Possible failure reasons: Problem with the query. “Result Set” property not set correctly, parameters not set correctly or connection not established correctly.

Error message was not looking fit for my scenario. In the mean while i got another report about errors being generated while creating tables on same server. The error message was pointing to failure of a DDL trigger meant for logging purpose. The trigger was being invoked on table creation and got failed due to permission issues while inserting logs in trigger log table. So i disabled the trigger and executed the task again. This time task was completed successfully.

So if you fall in above mentioned scenario then along with checking the reasons provided in error message also look for DDL trigger context.

  • Feb 16 / 2012
  • 0
dbDigger, Logins and Users, Logon Triggers, Security and Permissions, T-SQL Scripts, T-SQL Tips and Tricks, Triggers

Restrict logon for a SQL Server login through all but one IP

In a previous article i discussed that how to restrict SQL Server login to connect from out side the application. Logon trigger was utilize to achieve this task. The puprose behind it was to make sure that permission rich logins used in application should not be allowed to access SQL Server by any way other than managed application code.
However it is possible that any one may use that login through application for unit testing or to execute ad-hoc code from inside the application code. I was asked to also restrict such access that is adhoc or for unit testing from developers desktops. So the requirement is that login used in application could only access SQL Server after fulfilling two conditions

  1. Login is requesting to connect from inside the application code
  2. Login is requesting from no where else but web server (application server)

My previous article covers the first requirement but it lacks to check the second requirement. Following code (modified version of previous article) would make sure to check both the conditions prior to granting login access

 
CREATE TRIGGER [RestrictSSMSLogIn]  
 ON ALL SERVER WITH EXECUTE AS 'AppUser'  
 FOR LOGON  
 AS  
 BEGIN  
 DECLARE @ip VARCHAR(16)  
 SELECT @ip = client_net_address  
 FROM sys.dm_exec_connections WHERE session_id = @@SPID  
 IF ORIGINAL_LOGIN()= 'AppUser' AND  
 ((SELECT TOP 1 Program_Name  
 FROM sys.dm_exec_sessions  
 WHERE is_user_process = 1  
 AND original_login_name = 'AppUser'  
 Order By Session_Id Desc)  
 <>'.Net SqlClient Data Provider'  
 or @ip <> '192.168.1.202') -- Provide IP address of web server here  
 ROLLBACK;  
 END  
 GO  
 ENABLE TRIGGER [RestrictSSMSLogIn] ON ALL SERVER  
 GO  

In this way the login used for application is restricted to access only from web server through application code only.

  • Jan 07 / 2010
  • 0
DBA Interview questions, dbDigger, Triggers

Ordering the triggers in SQL Server database

I have 3 UPDATE triggers on a individual table, TRA, TRB and TRC. Can I fire them in the sequence TRA, TRB and TRC?

Answer: Yes

Explanation:Using sp_settriggerorder Set TRA as first, TRC as LAST and TRB will fire between TRA and TRC firing which is what is desired.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Oct 06 / 2009
  • 0
DBA best practices, DBA Interview questions, dbDigger, Publications of Atif Shehzad on MSSQLTips.com, Triggers

Prevent accidental update or delete commands of all rows in a SQL Server table

My article related to Prevent accidental update or delete commands of all rows in a SQL Server table is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Prevent accidental update or delete commands of all rows in a SQL Server table. It would provide efficient way to make sure that if you forget to include where clause in update or delete scripts then all rows should not be updated or deleted.

  • Jul 02 / 2009
  • 0
dbDigger, SSMS tips and tricks, T-SQL Scripts, Triggers

Enable or disable a DML Trigger through T-SQL and SSMS

I have created triggers to track changed data in various fields of several tables. Such triggers work at table level and are attached with table. While performing schedule updates through SQL Server agent jobs, i disable the triggers in the stored procedure that is performing given updates. It prevents the extra usage of triggers and also optimizes the space by skipping entries by such triggers.
Here is syntax to disable and enable trigger1 on table1

-- Disable the trigger  
 ALTER TABLE table1 DISABLE TRIGGER trigger1  
 GO  
 -- Enable the trigger  
 ALTER TABLE table1 ENABLE TRIGGER trigger1  
 GO  

Similarly another syntax may be used to enable and disable our imaginary trigger trigger1 on imaginary table table1 in this case

 
-- Disable the trigger  
 DISABLE TRIGGER trigger1 on table1  
 GO  
 -- Enable the trigger  
 ENABLE TRIGGER trigger1 on table1  
 GO  

Same task may be performed very easily through SSMS. Suppose trigger trigger1 is created on table table1. And we have to disable and enable it through SSMS

  • Open SSMS
  • Go to table on which trigger is defined
  • Open triggers folder under table

Disable and enable trigger through SSMS

  • Right click on trigger and click on Disable.

Disable trigger through SSMS

Confirmation dialog will be shown and icon of trigger will be slightly changed showing the change in trigger status. To enable the trigger through SSMS, again right click on disabled trigger and click Enable.

Enable trigger through SSMS

Again a confirmation dialog will appear and icon of trigger will be changed reflecting the change in status.

Pages:12
Consult us to explore the Databases. Contact us