Process to delete the logins which are not mapping to any database in sql server 2000

1   --- 1)      Run the below script

             ----Sp_helplogins

----a)      Copy the first table output in excel sheet and then apply filter on Auser with “no”.

2    ----2)      And then run the below script

select @@SERVERNAME

,sl.name

,isnull(DB_Roles.Roles,'Public')

from syslogins sl

left join

(

select 'sysadmin' as Roles

union all

select 'securityadmin'

union all

select 'serveradmin'

union all

select 'setupadmin'

union all

select 'processadmin'

union all

select 'diskadmin'

union all

select 'dbcreator'

union all

select 'bulkadmin'

union all

select 'No serverRole'

union all

select 'public'

) DB_Roles

on

Case When sl.sysadmin=1 and DB_Roles.Roles='sysadmin' then 'sysadmin'

      When sl.securityadmin=1 and DB_Roles.Roles='securityadmin' then 'securityadmin'

      When sl.serveradmin=1 and DB_Roles.Roles='serveradmin' then 'serveradmin'

      When sl.setupadmin=1 and DB_Roles.Roles='setupadmin' then 'setupadmin'

      When sl.processadmin=1 and DB_Roles.Roles='processadmin' then 'processadmin'

      When sl.diskadmin=1 and DB_Roles.Roles='diskadmin' then 'diskadmin'

      When sl.dbcreator=1 and DB_Roles.Roles='dbcreator' then 'dbcreator' 

      When sl.bulkadmin=1 and DB_Roles.Roles='bulkadmin' then 'bulkadmin'

      else null end=DB_Roles.Roles

     

order by 2

a)      Copy the result into excel sheet and apply the filter on third column with “sysadmin”

b)      Compare the both excel sheets and delete the logins in first excel sheet which are in second excel sheet.

c)       After that create a test database on the server with droplogin table with login_id as column name and entre the logins name which are in first excel sheet into the droplogin table.

d)      And run the below script on test database to delete the logins which are not map to any database.

declare @cursor cursor, @login_id sysname

set @cursor = cursor for (select login_id from droplogin)

open @cursor

while 1=1

 begin

 fetch next from @cursor into @login_id

 if @@FETCH_STATUS <> 0

  break

 EXEC ('sp_droplogin ' + @login_id)

 

 End

Note: Maintain the logins list which are we delete and perform the full backup on master database.