restrict windows authentication login

Question

restrict windows authentication login

DBA_1276

SSC Eights!

Points: 878
More actions
December 7, 2009 at 12:52 am

#214358

Hi all,
In sql server 2005 how can I restrict windows authentication login, allow only sql server logins.
Pls help me.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 1

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,
[font="Verdana"]Sqlfrenzy[/font]

DBA_1276 SSC Eights! Points: 878 More actions · Answer 2

DBA_1276

SSC Eights!

Points: 878

December 7, 2009 at 2:52 am

#1088412

Thank's for your replay.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

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
Consultant - Straight Path Solutions
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

DBA_1276 SSC Eights! Points: 878 More actions · Answer 4

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.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Here's a technet article about logon triggers.

Jack Corbett
Consultant - Straight Path Solutions
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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

Steve Jones - SSC Editor

SSC Guru

Points: 736270

December 8, 2009 at 8:50 am

#1089293

If you post the trigger code, we can help you debug it.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

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

DBA_1276 SSC Eights! Points: 878 More actions · Answer 8

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

pbedoya SSC Journeyman Points: 75 More actions · Answer 9

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 ...