restrict windows authentication login

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » restrict windows authentication login

restrict windows authentication login

Rate Topic

Display Mode

Topic Options

Author

Message

DBA_1276

Posted Monday, December 07, 2009 12:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 06, 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

Sqlfrenzy

Posted Monday, December 07, 2009 2:26 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:47 AM
Points: 1,431, Visits: 1,540

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

DBA_1276

Posted Monday, December 07, 2009 2:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 06, 2011 12:54 AM
Points: 158, Visits: 151

Thank's for your replay.

Post #829709

Jack Corbett

Posted Monday, December 07, 2009 7:43 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

DBA_1276

Posted Tuesday, December 08, 2009 2:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 06, 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

Jack Corbett

Posted Tuesday, December 08, 2009 7:54 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Here's a technet article about logon triggers.

Post #830693

Steve Jones - SSC Editor

Posted Tuesday, December 08, 2009 8:50 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 4:46 PM
Points: 31,433, Visits: 13,745

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

Elliott Whitlow

Posted Tuesday, December 08, 2009 9:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

DBA_1276

Posted Tuesday, December 08, 2009 9:10 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 06, 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

pbedoya

Posted Wednesday, December 09, 2009 12:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 05, 2011 5:49 PM
Points: 3, Visits: 24

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 »

Permissions