Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

restrict windows authentication login Expand / Collapse
Author
Message
Posted Monday, December 7, 2009 12:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 6, 2011 12:54 AM
Points: 158, Visits: 151
Hi all,
In sql server 2005 how can I restrict windows authentication login, allow only sql server logins.
Pls help me.
Post #829682
Posted Monday, December 7, 2009 2:26 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, October 23, 2014 12:38 PM
Points: 1,475, Visits: 1,640
you 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.




Regards,
Sqlfrenzy

Post #829705
Posted Monday, December 7, 2009 2:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 6, 2011 12:54 AM
Points: 158, Visits: 151
Thank's for your replay.
Post #829709
Posted Monday, December 7, 2009 7:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 10,340, Visits: 13,341
You 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:

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





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #829854
Posted Tuesday, December 8, 2009 2:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 6, 2011 12:54 AM
Points: 158, Visits: 151
HI,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.
Post #830482
Posted Tuesday, December 8, 2009 7:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 10,340, Visits: 13,341
Here's a technet article about logon triggers.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #830693
Posted Tuesday, December 8, 2009 8:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:55 PM
Points: 31,278, Visits: 15,736
If you post the trigger code, we can help you debug it.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #830748
Posted Tuesday, December 8, 2009 9:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
You 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..

CEWII
Post #830768
Posted Tuesday, December 8, 2009 9:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 6, 2011 12:54 AM
Points: 158, Visits: 151
Hi,thank's for your reply
here u can find my trigger code.

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN

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
END
Post #831147
Posted Wednesday, December 9, 2009 12:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:24 PM
Points: 3, Visits: 25
Must be:

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER FOR DDL_LOGIN_EVENTS

I have gotten an error with "LOGON" clause...However msdn tell us:
CREATE TRIGGER XYZ
ON ALL SERVER {FOR|AFTER} LOGON ...
Post #831757
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse