:::: MENU ::::

Posts Categorized / T-SQL Scripts

  • 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 22 / 2009
  • 0
Constraints and Keys, Data Modeling and Database Design, dbDigger, SSMS tips and tricks, T-SQL Scripts

Create unique key constraint through T-SQL and SSMS

There may be required to make sure that to fulfill a certain business logic, each record should be unique in terms of some columns. To implement this there is no need to create any primary key having combination of columns. Simply a unique key constraint may be created.
To consider an example of creating unique key constraint let us create a sample table

 
USE AdventureWorks  
 GO  
 CREATE TABLE TestUKey(serNumb SMALLINT IDENTITY(1,1),   
 name VARCHAR(50),  
 city VARCHAR(50),  
 phone VARCHAR(13),  
 cellNumb VARCHAR(13))  
 GO  

Suppose now it is required to make sure that each record have unique combined values in name and cellnumb. To implement this constraint we may use following T-SQL statement
[/sql] USE AdventureWorks
GO
ALTER TABLE TestUKey
ADD CONSTRAINT IX_TestUKey_NameCell
UNIQUE(Name, CellNumb)
GO
[/sql]
Our required logic has been implemented and to confirm it following is a part of result from <

sp_help TestUKey

Unique key constraint confirmation

To create unique key constraint through SQL Server Management Studio (SSMS), following are required steps.
Drop existing unique key constraint so that we may create unique key constraint through SSMS

 
USE AdventureWorks  
 GO  
 ALTER TABLE TestUKey  
 DROP CONSTRAINT IX_TestUKey_NameCell  
 GO  

Now we are ready to create unique key constraint through SSMS

  • Right click on table on which constraint will be implemented and go to Design
  • Right click on left bar of design to access indexes and keys or access from tool bar

Create unique key constraint through SSMS 1

  • A frame will appear. Choose both columns and select unique key in type.
  • Close the frame and exit from designer.

Unique key has been created and this may be verified through SSMS

Confirm unique key constraint through SSMS

I have noticed that along with each unique key created a non clustered index is automatically created comprising the columns of unique key.
Click here for another example of unique key constraint implementation.

Consult us to explore the Databases. Contact us