here's another example i posted that has additional logic, and check out the thread it came from for more info:

http://www.sqlservercentral.com/Forums/Topic927131-359-1.aspx

in this case, i'm trying to prevent SSMS and also unauthorized 'sa' usage.

it's got some exceptions, like SSMS is ok if the user is My domain login or sa, and also that yhose logins are connecting from a specific machine.

--Prevent access from SSMS

--drop TRIGGER logon_trigger_not_from_SSMS on all server

CREATE TRIGGER logon_trigger_not_from_SSMS

ON ALL SERVER FOR LOGON

AS

BEGIN

IF APP_NAME() LIKE '%Microsoft SQL Server%'

BEGIN

IF suser_name() IN ('Stormdev\Lowell','sa') --the only persons allowed to use SSMS, no sa allowed for testing

BEGIN

--only allowed from my host machine

IF host_name() !='STORMDEV'

BEGIN

RAISERROR('SSMS connections are restricted on to specific dba machines.', 16, 1)

ROLLBACK

END --host name check

END --suser_name check

ELSE

BEGIN

RAISERROR('SSMS connections are not permitted with this logon.', 16, 1)

ROLLBACK

END

END --app name check

ELSE

BEGIN

RAISERROR('SSMS connections are restricted on this server.', 16, 1)

ROLLBACK

END

END --trigger