Preventing login to server from a client PC (1 Viewer)

[KitaYama]

It's certainly easier to restrict access by IP address in servers like MySQL, but you can do it with triggers/firewall rules in SQLServer.

See search results

@cheekybuddha I don't think so. In a windows domain, if DHCP is in place, The IP addresses are set by DHCP server which means the same machine may have a different IP on each login. Even if the domain is set to use static IPs, any client PC can change it's address simply by UA or a script from shell. In most cases that I know, clients have admin rights for their own machines which gives them the right to change their IPs.

So even if sql server admin, restrict some IPs, still there's a good chance somebody with a different IP can log into sql server with SSMS or similar client applications.

 

[KitaYama]

With hints given by @WayneRyan and @cheekybuddha on login triggers in sql Server I added the following login trigger, still am facing the same problem.

After adding the trigger, only MyUserName can login to server. (MyUserName is windows login name)
When I run FE from any other pc, I receive a message that login to sql server failed.
If I remove the trigger, everything is back to normal.

CREATE OR ALTER TRIGGER LogonTrigger_For_Audit ON ALL SERVER FOR LOGON
AS
BEGIN
    DECLARE @login NVARCHAR(200)
    SET @login=ORIGINAL_LOGIN()

    IF @login <> 'MyDomaninName\MyUserName'
        BEGIN
            INSERT INTO Master.dbo.tblLogonAudit (UserName, LogonTime,spid)
                VALUES (@login, GETDATE(),@@SPID);
            print @login + ' failed attempted login'
            ROLLBACK
        END
END

Any Other choices?

Up to now it seems that @Pat Hartman 's solution is the only possible solution.

Thanks again.