locked
Disable All Logins (I'm getting an error with my script) RRS feed

  • Question

  • I have the following query which loops through the logins and attempts to disable them with some accounts being excluded.  The problem is that I'm using dynamic SQL and when I run it, I'm having trouble disabling the logins that have the type Domain\Account.  The error is Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '\'.

    Set NOCOUNT ON

    --Check if temp table exists
    IF object_id('tempdb..#LoginNames') is NOT NULL
    Drop Table #LoginNames
    Go


    --Create the temp table

    Create Table #LoginNames (
    RowID int Identity(1, 1)
    ,[LNames] varchar(100)
    )
    GO

    --Create the variables for the While Loop count

    Declare @NumberofRecords int
    Declare @RowCount int
    Declare @LGname2 nvarchar(100)
    Declare @Command nvarchar(4000)

    --Insert the result set we want to loop into the temp table

    Insert Into #LoginNames(LNames)
    Select name from sys.syslogins
    Where name NOT IN (
     '##MS_SQLResourceSigningCertificate##'
    ,'##MS_SQLReplicationSigningCertificate##'
    ,'##MS_SQLAuthenticatorCertificate##'
    ,'##MS_PolicySigningCertificate##'
    ,'##MS_SmoExtendedSigningCertificate##'
    ,'##MS_PolicyTsqlExecutionLogin##'
    ,'##MS_PolicyEventProcessingLogin##'
    ,'##MS_AgentSigningCertificate##' 
    ,'NT SERVICE\SQLWriter'
    ,'NT SERVICE\Winmgmt'
    ,'NT SERVICE\MSSQLSERVER'
    ,'NT AUTHORITY\SYSTEM'
    ,'NT SERVICE\SQLSERVERAGENT'
    )

    -- Get the number of records
    Set @NumberofRecords = @@ROWCOUNT
    Set @RowCount = 1

    --Loop Through the record set
    While @RowCount <= @NumberofRecords
    Begin

    Set @LGname2 = (Select [LNames] from #LoginNames where RowID = @RowCount)
    Set @Command = 'ALTER LOGIN ' + @LGname2 + ' enable'
    Exec (@Command)
    Set @RowCount = @RowCount +1
    End


    Friday, August 19, 2016 5:42 PM

Answers

  • The error is Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '\'.

    Hello Diango,

    You have to quote all Windows login names + all with invalid characters; as it best all logins =>

    ...
    Insert Into #LoginNames(LNames)
    Select QUOTENAME(name) from sys.syslogins
    Where name NOT IN (...

    Just being curios: Why disabling all (incl "sa")? If you don't what anyone to logon to SQL Server then shut it down, that's easier.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Diango Friday, August 19, 2016 7:16 PM
    Friday, August 19, 2016 6:47 PM

All replies

  • The error is Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '\'.

    Hello Diango,

    You have to quote all Windows login names + all with invalid characters; as it best all logins =>

    ...
    Insert Into #LoginNames(LNames)
    Select QUOTENAME(name) from sys.syslogins
    Where name NOT IN (...

    Just being curios: Why disabling all (incl "sa")? If you don't what anyone to logon to SQL Server then shut it down, that's easier.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Diango Friday, August 19, 2016 7:16 PM
    Friday, August 19, 2016 6:47 PM
  • Hi Olaf,

    That worked thank you!  So the reason why I don't just shut it all down is because it's a DR Server and we need it up and running because we're doing log shipping!  That's why I want to disable most of the accounts except for a few logins, which would include my group.  We are disabling the SA account as a security method best practices.  Thanks again!

    Friday, August 19, 2016 7:15 PM