InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 23:04:18 GMT20http://www.sqlservercentral.com/Forums/Topic829682-146-1.aspxMust be: CREATE TRIGGER connection_limit_triggerON ALL SERVER FOR DDL_LOGIN_EVENTS I have gotten an error with "LOGON" clause...However msdn tell us: CREATE TRIGGER XYZON ALL SERVER {FOR|AFTER} LOGON ...Wed, 09 Dec 2009 12:49:58 GMTpbedoyahttp://www.sqlservercentral.com/Forums/Topic829682-146-1.aspxHi,thank's for your reply here u can find my trigger code.CREATE TRIGGER connection_limit_triggerON ALL SERVER FOR LOGONASBEGINIF EXISTS(SELECT * FROM sys.server_principals AS SP WHERE [name] = SUSER_SNAME() AND type_desc IN ('Windows_Login', 'Windows_Group')) BEGIN RAISERROR('Windows LOGIN''s are NOT allowed ON this SERVER', 16, 1) ENDENDTue, 08 Dec 2009 21:10:39 GMTDBA_1276http://www.sqlservercentral.com/Forums/Topic829682-146-1.aspxYou want to keep in mind how SQL agent logs in.. so you might not want to absolutely deny all windows logins but deny all BUT a limited list..CEWIITue, 08 Dec 2009 09:07:51 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic829682-146-1.aspxIf you post the trigger code, we can help you debug it.Tue, 08 Dec 2009 08:50:28 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic829682-146-1.aspxHere's a [url=http://technet.microsoft.com/en-us/library/bb326598.aspx]technet article[/url] about logon triggers.Tue, 08 Dec 2009 07:54:10 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic829682-146-1.aspxHI,thank's for your repaly,I am trying to create logon trigger form my sysadmin login but it gives error like Msg 1084, Level 15, State 1, Procedure Tr_ServerLogon, Line 2'LOGON' is an invalid event type.Tue, 08 Dec 2009 02:09:29 GMTDBA_1276http://www.sqlservercentral.com/Forums/Topic829682-146-1.aspxYou can't disable the ability to have Windows Logins. You can only not create Logins linked to Windows accounts. You could also write a Logon Trigger that checks the login type and denies access to Windows Logins. It would contain information sort of like this:[code="sql"]IF EXISTS(SELECT * FROM sys.server_principals AS SP WHERE [name] = SUSER_SNAME() AND type_desc IN ('Windows_Login', 'Windows_Group')) BEGIN RAISERROR('Windows LOGIN''s are NOT allowed ON this SERVER', 16, 1) END [/code]Mon, 07 Dec 2009 07:43:14 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic829682-146-1.aspxThank's for your replay.Mon, 07 Dec 2009 02:52:23 GMTDBA_1276http://www.sqlservercentral.com/Forums/Topic829682-146-1.aspxyou can remove the built\Administrator role, though before deleting delete the users in each database related to the above server login and transfer the ownership to new users; also delete all windows authenticated server login. Mon, 07 Dec 2009 02:26:18 GMTSqlfrenzyhttp://www.sqlservercentral.com/Forums/Topic829682-146-1.aspxHi all, In sql server 2005 how can I restrict windows authentication login, allow only sql server logins.Pls help me.Mon, 07 Dec 2009 00:52:29 GMTDBA_1276