locked
Disable all logins (except 1) RRS feed

  • Question

  • Hi there!

    Im need to restart an instance in single user mode, but there are hundreds of logins on the instance which seem to automatically grab the single connection available. I need to disable them all (except for my own connection).

    Is there a script I can do this with, rather than go through each one individually?

    Thanks,

    Zoe

    Wednesday, March 21, 2012 10:08 AM

Answers

  • got it

    declare @command nvarchar(1024)
    declare @name nvarchar(64)

    DECLARE db_cursor CURSOR FOR 
    SELECT name FROM sys.server_principals

    WHERE name NOT IN ('public','sysadmin','securityadmin','serveradmin', 'setupadmin', 'processadmin', 'diskadmin', 'dbcreator', 'bulkadmin',
    'dbcreator', 'bulkadmin') 

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @name  

    WHILE @@FETCH_STATUS = 0  
    BEGIN
    set @command = 'ALTER LOGIN ' +  @name + ' disable'

    EXECUTE sp_executesql @command


           FETCH NEXT FROM db_cursor INTO @name  
    END  

    CLOSE db_cursor  
    DEALLOCATE db_cursor

    Thanks for all your help people!

    • Marked as answer by Zoe.Ohara Wednesday, March 21, 2012 11:33 AM
    Wednesday, March 21, 2012 11:33 AM

All replies

  • Pls try

    SELECT 'ALTER LOGIN '+ QUOTENAME(name)+' DISABLE;' 
    FROM sys.syslogins where (execlusion list here)..

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by SimpleSQL Wednesday, March 21, 2012 10:18 AM
    Wednesday, March 21, 2012 10:16 AM
  • Hi

        This query will list all login:

    SELECT * FROM sys.server_principals

        This will disable login:

    ALTER LOGIN [loginname] DISABLE

        Just create cursor that iterates through your logins and disables it.


    everything is a matter of probability...

    Wednesday, March 21, 2012 10:17 AM
  • Thank you!

    Ill try the cursor thing. If I figure it out ill post it here

    Thanks!

    x

    Wednesday, March 21, 2012 10:31 AM
  • Can anyone tell me whats wrong with this:


    declare @name nvarchar(64)

    DECLARE db_cursor CURSOR FOR 
    SELECT name
    FROM MASTER.dbo.sysdatabases
    WHERE name NOT IN ('public','sysadmin','securityadmin','serveradmin', 'setupadmin', 'processadmin', 'diskadmin', 'dbcreator', 'bulkadmin',
    'dbcreator', 'bulkadmin') 

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @name  

    WHILE @@FETCH_STATUS = 0  
    BEGIN  
       ALTER LOGIN @name DISABLE

           FETCH NEXT FROM db_cursor INTO @name  
    END  

    CLOSE db_cursor  
    DEALLOCATE db_cursor

    I get the error

    sg 102, Level 15, State 1, Line 15


    Incorrect syntax near '@name'.

    Wednesday, March 21, 2012 11:01 AM
  • got it

    declare @command nvarchar(1024)
    declare @name nvarchar(64)

    DECLARE db_cursor CURSOR FOR 
    SELECT name FROM sys.server_principals

    WHERE name NOT IN ('public','sysadmin','securityadmin','serveradmin', 'setupadmin', 'processadmin', 'diskadmin', 'dbcreator', 'bulkadmin',
    'dbcreator', 'bulkadmin') 

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @name  

    WHILE @@FETCH_STATUS = 0  
    BEGIN
    set @command = 'ALTER LOGIN ' +  @name + ' disable'

    EXECUTE sp_executesql @command


           FETCH NEXT FROM db_cursor INTO @name  
    END  

    CLOSE db_cursor  
    DEALLOCATE db_cursor

    Thanks for all your help people!

    • Marked as answer by Zoe.Ohara Wednesday, March 21, 2012 11:33 AM
    Wednesday, March 21, 2012 11:33 AM