:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

  • Jun 26 / 2009
  • 0
dbDigger, Security and Permissions, T-SQL Scripts, T-SQL Tips and Tricks, Triggers

How to disable or enable SQL Server logon trigger

In my previous post we discussed process to implement logon trigger for different purposes. Later some time it may be required to skip the customizations implemented through logon trigger. So for this you may disable the logon trigger rather than to drop it.
If we want to disable the trigger [RestrictSSMSLogIn] that we created during previous post, following is T-SQL command to disable the logon trigger

 
DISABLE TRIGGER RestrictSSMSLogin  
 ON ALL SERVER   
 GO  

You may use following T-SQL command to enable the logon trigger later at any time

 
ENABLE TRIGGER RestrictSSMSLogin  
 ON ALL SERVER   
 GO  
  • Jun 25 / 2009
  • 0
dbDigger, Logins and Users, Logon Triggers, Security and Permissions, T-SQL Scripts, T-SQL Tips and Tricks, Triggers

Restrict SQL Server login to connect from out side the application through logon trigger

There is a situation that I have a SQL Server login with rich rights in some of databases. It is used for connection through a .Net application. It is not assigned to any one for use in SSMS etc. I want to make sure that this login would not be used for connection to SQL Server out side the application. While looking at various possible options i ca,e to know LogOn triggers in SQL Server.
Logon triggers in SQL Server are used to carry out logon related customization and tasks. From onwards to SQL Server 2005 SP2, we can create logon triggers that are meant to be fired every time when a session is established to SQL Server. If a login is not successfully authenticated then logon trigger is not fired in that case.
Consider following example to implement the above requirement. We will restrict the user AppUser to connect to SQL Server through .Net application only.

 
CREATE TRIGGER [RestrictSSMSLogIn]  
 ON ALL SERVER WITH EXECUTE AS 'AppUser'  
 FOR LOGON  
 AS  
 BEGIN  
 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'  
 ROLLBACK;  
 END  
 GO  
 ENABLE TRIGGER [RestrictSSMSLogIn] ON ALL SERVER  
 GO  

Trigger has been created and user ‘AppUser’ is no more able to connect to SQL Server through any means other than .Net application.
Logic implemented in if block may be modified to implement a large number of other customizations that are required.
Logon triggers are created in master database. And may be viewed in SSMS in server objects folder.

LogOn Trigger in SSMS

While trying to log in SSMS through logon trigger following message is generated, showing that created logion trigger is working fine.

LogOn Restricted through LogOn trigger

It is important to mention that be attentive while working with logon trigger, some wrong or missing conditions may block all SQL Server logins to log in to SQL Server. Best solution is that be logged in through any user in SSMS while testing logon trigger. If any problem occurs due to trigger you can use already logged in user to manage the situation.
Even if you do not have a log in connected and a problem occurs due to logon trigger then work according to this article.
Click here
to get T-SQL commands to enable or disable the SQL Server logon trigger

  • Jun 22 / 2009
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

How to get duplicate rows from a table

In one of my previous articles, i discussed that how to remove duplicate rows from a table. While analyzing the duplicate rows based on some columns, often it is required to get whole list of columns even if they are not included in our duplicate criteria.
Instead of creating a table and populating it with data, i will simply go through the concept through a sample script only to get the list.

 
SELECT --comma separated list of all columns to select   
 FROM table1 a  
 join   
 (SELECT col1, col2, col3   
 FROM table1  
 GROUP BY col1, col2, col3   
 HAVING count(*) > 1) b   
 ON a.col1 = b.col1  
 and a.col2 = b.col2  
 and a.col3 = b.col3  
 ORDER BY a.col1, a.col2, a.col3  
 GO  

Where col1, col2, col3 are columns on which duplicate rows are determined in table1.

  • Jun 06 / 2009
  • 0
dbDigger, T-SQL Tips and Tricks

Make required delay in T-SQL with WAITFOR DELAY

For many tasks some delay between tasks is required. For such required delay use WAITFOR DELAY option. It may be used on all versions of SQL Server. Syntax for this option is

 
WAITFOR   
 {  
 DELAY 'time_to_pass'   
 | TIME 'time_to_execute'   
 | [ ( receive_statement ) | ( get_conversation_group_statement ) ]   
 [ , TIMEOUT timeout ]  
 }  

For example to execute two T-SQL statements with a delay of 5 seconds we have following example.

 
SELECT 'Written before delay' as BeforeDelay  
 GO  
 WAITFOR DELAY '00:00:05'   
 GO  
 SELECT 'Written after delay' as AfterDelay  
 GO  

Executing above script will display two results with a difference of 5 seconds. For another example we may use it for a single GO in following way.

SELECT 'Displayed after delay' as AfterDelay  
 WAITFOR DELAY '00:00:05'   
 GO  

In this way whole statement will be executed with 5 seconds delay.
WAITFOR DELAY is a powerful option and needs to be used carefully. It may generate dead lock if not used properly. Especially while using it in step with other T-SQL statement as in case of our second example. Some limited and safe use of this option may be

  • Use WAITFOR DELAY as a job step between other job steps to provide a delay between DML operations and backups. It will reduce load on resources.
  • Similarly use WAITFOR DELAY between GO statements of any T-SQL script for delay.

Keeping in view the basic functionality you may use this option as required, but for complicated dependent tasks it would be better to use SQL Srever agent scheduling tools or service broker for different versions of SQL Server.

  • Jun 03 / 2009
  • 0
dbDigger, Security and Permissions, T-SQL Tips and Tricks, User Defined Views

Implement required restrictions on view by using WITH CHECK OPTION

I have created views for employees in different departments. Each employee is able to view records for his own department in the view. Also employees can update or insert records for their department through that view. I noticed that employees are even update and insert data that is related to any other department.
Now it was required to restrict that no employee can insert/update data that conflicts with department restriction that i have implemented in each view. For example consider the following imaginary view where records are picked based on a department provided in where clause

 
Create VIEW UVW_DeptRecords  
 AS  
 SELECT name, designation, department  
 FROM employees   
 WHERE department = 'Finance'  
 GO  

Employees from finance department are able to perform DML operations on this view. It is required that these employees may not be able to insert a record with department name other than Finance. First i planned to use a trigger for implementation of this logic. But while discussing this issue with fellow DBA i came to know that there is an option provided with the view definition itself, that will prohibit any DML that conflicts with the conditions provided in where clause of view definition.
So creating the above view with additional WITH CHECK OPTION in following way will not allow any DML operation for which employee does not belong to Finance department.

 
Create VIEW UVW_DeptRecords  
 AS  
 SELECT name, designation, department  
 FROM employees   
 WHERE department = 'Finance'  
 WITH CHECK OPTION  
 GO  
Consult us to explore the Databases. Contact us