locked
Script to delete all logins with no user mappings in sql server 2000 RRS feed

  • Question

  • Hi,

    I would like to delete all logins with no user mappings in sql server 2000.

    Please help me with the script that deletes all logins with no user mappings in sql 2000 server.

    Thanks,


    rup


    • Edited by rupc Tuesday, February 12, 2013 3:38 AM
    Monday, February 11, 2013 3:59 PM

Answers

  • Something like this. Scrutinize the list before you actually drop:

    CREATE TABLE #sid (sid binary(85) NOT NULL)

    INSERT #sid (sid)
    EXEC sp_MSforeachdb 'select sid from sysusers WHERE sid IS NOT NULL'

    SELECT 'DROP LOGIN ' + quotename(name)
    FROM   master.dbo.syslogins l
    WHERE  NOT EXISTS (SELECT *
                       FROM   #sid s
                       WHERE  s.sid = l.sid)
    AND name NOT LIKE '##%'
    AND name NOT LIKE 'NT %'

    go
    DROP TABLE #sid


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Iric Wen Wednesday, February 20, 2013 8:40 AM
    Monday, February 11, 2013 11:06 PM

All replies

  • Something like this. Scrutinize the list before you actually drop:

    CREATE TABLE #sid (sid binary(85) NOT NULL)

    INSERT #sid (sid)
    EXEC sp_MSforeachdb 'select sid from sysusers WHERE sid IS NOT NULL'

    SELECT 'DROP LOGIN ' + quotename(name)
    FROM   master.dbo.syslogins l
    WHERE  NOT EXISTS (SELECT *
                       FROM   #sid s
                       WHERE  s.sid = l.sid)
    AND name NOT LIKE '##%'
    AND name NOT LIKE 'NT %'

    go
    DROP TABLE #sid


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Iric Wen Wednesday, February 20, 2013 8:40 AM
    Monday, February 11, 2013 11:06 PM
  • Extended support for SQL 2000 is ending soon. Please consider upgrading your SQL 2000 instance to SQL 2008 R2

    http://blogs.msdn.com/b/sqlagent/archive/2013/01/22/sql-server-2000-extended-support-for-sql-server-2000-ending-soon.aspx


    Thanks, Sethu Srinivasan [MSFT] SQL Server http://blogs.msdn.com/sqlagent -------------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, February 13, 2013 7:56 PM