Restrict SQL Server Login –SQL Server 2005

There is always a need to restrict the user logins into the SQL Server databases. Many DBA’s face this difficulty, as the restriction of logins has to be done for different users with different requirements. SQL Server 2005 introduces an option called “LOGON” triggers which is an easy way restrict the number of user logins as per our requirements.

This Logon trigger is created directly on the database server and registered on the master database. The below sample demonstrates the use of login triggers to restrict the user “john” from accessing the database using “SQL Query Analyzer” window.


USE master
GO
CREATE TRIGGER trgRestrictUser
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF (ORIGINAL_LOGIN()= 'john' AND APP_NAME() = 'Microsoft SQL Server Management Studio - Query')
ROLLBACK;
END;

This logon trigger can be used for various auditing purposes in SQL Server. This is a new feature introduced in the SQL Sever 2005 Service Pack 2.We need to upgrade to SP2 to use this feature.

Below links can give more information about Logon triggers
http://msdn2.microsoft.com/en-us/library/bb326598.aspx