i get the same thing. i created a trigger with the code below and have on row in the table i want to use as a blacklist.

we are getting ready for SOX and need to lock devs out of production databases. problem is that it's easy to figure out the encrypted passwords for the logins and they insist on accessing production, even if it's for troubleshooting purposes.

plan will be on each production server to create a table with hostnames, sql logins and app names and then create login triggers that will fire only in very specific conditions.

copied the code below from BOL and modified just a bit. problem is that when i run it the trigger locks everyone out and i have to go through the DAC to disable it.

CREATE TRIGGER connection_limit_trigger

ON ALL SERVER WITH EXECUTE AS 'sql_login'

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN()= 'sql_login' AND host_name() in (select hostname from admindb..sqllogin_hostname_blacklist)

ROLLBACK

END