SQL 2005 sql login ip restriction

Question

Is there a way to restrict a specific SQL 2005 login on a Microsoft SQL Server 2005, standard version (sql is in mixed mode) to specific IP addresses, while other logins, Windows authenticated ones, are unaffected?

Answer 1

I use the following trigger to restrict access to specific login - IPs combo.

CREATE TRIGGER [LOGIN_IP_RESTRICTION]
    ON ALL SERVER FOR LOGON
AS
BEGIN
    DECLARE @host NVARCHAR(255);

    SET @host = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(max)');

    IF(EXISTS(SELECT * FROM master.dbo.IP_RESTRICTION 
        WHERE UserName = SYSTEM_USER))
    BEGIN
        IF(NOT EXISTS(SELECT * FROM master.dbo.IP_RESTRICTION 
            WHERE UserName = SYSTEM_USER AND ValidIP = @host))
        BEGIN
            ROLLBACK;
        END
    END
END;

DDL for table IP_RESTRICTION:

CREATE TABLE [dbo].[IP_RESTRICTION](
    [UserName] [varchar](255) NOT NULL,
    [ValidIP] [varchar](15) NOT NULL,
    [Comment] [nvarchar](255) NULL,
 CONSTRAINT [PK_IP_RESTRICTION] PRIMARY KEY CLUSTERED 
([UserName] ASC, [ValidIP] ASC) ON [PRIMARY]
) ON [PRIMARY]