Sql Server script to automate the removal of logins

Use master
if exists (select *
             from dbo.sysobjects
            where id = object_id(N’[dbo].[sp_RemoveLogin]‘)
              and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
   drop procedure [dbo].[sp_RemoveLogin]
Go

Create procedure sp_RemoveLogin
       @name sysname = null
As
 
/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/
–Name        : sp_RemoveLogin        for SQL 7.0 & 2K
–
–Description : Attempts to remove a login from a SQL Server whether STD or NT.
–
–Parameters  : @name – the login to be removed, ie.e, <login> or
–                     
<domain>\<login>
–
–Comments    : Removing login from a SQL server can be a tedious, manual
–             
process checking for database access in each database, object
–             
ownership in each database, granted permissions (the login is
–             
the grantor), jobs & packages owned by the login. This procedure
–             
automates the process as much as possible. The following rules
–              are applied when issues are encountered:
–             
1) If the login owns databases (as will occur when a restore
–                
is done manually) the ownership is changed to sa.
–             
2) If the login is a user in a particlar db and owns objects,
–                
then the proc attempts to reassign ownership to dbo. If an
–                
object by the same name is already owned by dbo a message is
–                
displayed and manual intervention is required.
–             
3) If this login as a user in a db has granted permissions then
–                
those permissions are removed.
–             
4) Once object ownership is taken care and grants are dropped
–                
then the user can be removed from the db.
–              5) If the user is aliased it is dropped.
–             
6) This process continues for each db. Once all dbs are
–                
processed if there were any objects that could not be handle 
–                
without manual intervention a message is displayed to that
–                 effect.
–             
7) If the login owns jobs or packages in msdb those are changed
–                 to sa.
–             
Any open connections the login has are killed and finally the
–                
login is removed from the SQL Server.
–             
9) If a session could not be killed a message is displayed to that
–                 effect.
–
–Date        : 07/02/2001
–Author      : Clinton Herring
–
–History     : 07/10/2002 Added code to change the db owner to sa if the
–                        
login owns databases.
–
/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/

– Create a temp holding tables
If (Select object_id(‘tempdb.dbo.#Parm’)) > 0
   Exec (‘Drop table #Parm’)
Create table #Parm(value int null)

– Declare variables   
Declare @sid varbinary(85),
        @dbname sysname,
        @cmd varchar(4096),
        @spid int

– Check for master db
If db_name() <> ‘master’
   Begin
      Print ‘This stored procedure must be run from the master database.’
      Return
   End

– Check for a null parameter
IF @name is null
   Begin
      Print ‘This stored procedure requires a valid login as a parameter.’
      Return
   End

– Check for logins not allowed to be dropped using this procedure
IF @name in (‘BUILTIN\Administrators’, ‘distributor_admin’, ‘sa’, ‘repl_publisher’, ‘repl_subscriber’)
   Begin
      Print ‘You may not drop the following logins using this stored procedure:’
     
Print ‘   BUILTIN\Administrators, distributor_admin, sa,
repl_publisher, repl_subscriber’
      Return
   End

– Check to see if the login exists.
If exists (select * from master.dbo.syslogins where loginname = @name)
   Begin

      — Display a message
      Print ‘Attempting to find and drop ”’ + @name + ”’ from each database…’

      — retrieve the sid of the login
      Set @sid = suser_sid(@name)

      — Does this login own any databases
      If exists(select * from sysdatabases where sid = @sid)
         Begin
           
Select @cmd = ‘use master declare @cmd varchar(512) Exec sp_configure
”allow updates”,1 ‘ +
                         
‘Reconfigure with override Waitfor delay ”00:00:01” ‘ +
                         
‘Print ”   Fixing db owner issues in master…” ‘ +
                         
‘Select @cmd = ”Update sysdatabases set sid = 0×01 where sid =
suser_sid(””’ + @name + ””’)” ‘ +
                         
‘Exec (@cmd) Exec sp_configure ”allow updates”,0 Reconfigure with
override ‘
            Exec (@cmd)
         End           

      — If the login exists begin checking each database for this login as a users in
      — that database.
      Select @dbname = min(name) from master.dbo.sysdatabases

      — Loop through each database.
      While @dbname is not null
         Begin

            — Here dynamic sql is required to use the ‘Use command’.
           
– This loop checks for db and msdb ownership issues & granted
permissions.
            — Build a command.
           
Select @cmd  = ‘use ‘ + @dbname + ‘ declare @uid int, @cmd
varchar(512), @name sysname ‘ +
                          
‘If exists (select * from sysusers where sid = suser_sid(”’ + @name +
”’) and isaliased = 0) ‘ +
                          
‘Begin Print ”   Processing db ‘ + @dbname + ‘…” Select
@uid = uid, @name = name from ‘ +
                          
‘sysusers where sid = suser_sid(”’ + @name + ”’) If exists (select *
from sysobjects ‘ +
                          
‘where uid = 1 and name in (select name from sysobjects where uid =
@uid)) ‘ +
                          
‘Begin Print ”   The following objects are owned by the user
in database ‘ + @dbname + ‘.” ‘ +
                          
‘Print ”   Objects with the same name owned by dbo already
exist. Please decide ” ‘ +
                          
‘Print ”   what to do with these objects before attempting
to drop this user.” Print ”” ‘ +
                          
‘Select convert(varchar(50), name) ”name”, type from sysobjects where
uid = @uid ‘ +
                          
‘Insert into #parm values(1) End ‘ +
                          
‘Else Begin Exec sp_configure ”allow updates”, 1 Reconfigure with
override ‘ +
                          
‘waitfor delay ”00:00:01” select @cmd = ”update sysobjects set uid =
1 where uid = ” ‘ +
                          
‘+ convert(varchar(5),@uid) + ‘ +
                          
”’ Delete from syspermissions where grantor = ” +
convert(varchar(5),@uid) ‘ +
                          
‘Print ”   Fixing object ownership issues in ” + db_name()
+ ”…” Exec (@cmd) ‘ +
                          
‘Exec sp_configure ”allow updates”, 0 Reconfigure with override ‘ +
                          
‘Exec sp_revokedbaccess @name End Print ”” End ‘ +
                          
‘If exists(select * from sysusers where sid = suser_sid(”’ + @name +
”’) and isaliased = 1) ‘ +
                          
‘Begin Exec sp_dropalias ”’ + @name + ”’ Print ””
End’                     

            — Execute the command
            Exec (@cmd)

           
– If the database is msdb then fix any job or package onwership issues.
            If @dbname = ‘msdb’ and
              
(exists(select * from msdb.dbo.sysjobs where owner_sid = @sid) or
               
exists(select * from msdb.dbo.sysdtspackages where owner_sid = @sid))
               Begin
                 
Select @cmd = ‘use msdb declare @cmd varchar(512) ‘ +
                               
‘Exec sp_configure ”allow updates”, 1 Reconfigure with override ‘ +
                               
‘waitfor delay ”00:00:01” select @cmd = ‘ +
                               
”’update sysdtspackages set owner = ””sa””, owner_sid = ‘ +
                               
’0×01 where owner_sid = suser_sid(””’ + @name + ””’) ‘ +
                               
‘update sysjobs set owner_sid = 0×01 where owner_sid = suser_sid(””’
+ @name+ ””’)” ‘ +
                               
‘Print ”   Fixing job &/or package ownership issues in
msdb.” ‘ +
                               
‘Exec (@cmd) Exec sp_configure ”allow updates”, 0 Reconfigure with
override ‘
                  Exec (@cmd)
               End

           
Select @dbname = min(name) from master.dbo.sysdatabases where name >
@dbname
         End
     
      — Did we have any issues that could not be resolved?
      If exists(select * from #parm where value = 1)
         Print ‘Cannot drop the login at this time.’
      Else
         Begin
            Truncate table #parm

           
– Check for any connection by this login and attempt to kill them.
           
If exists (Select * from master.dbo.sysprocesses where loginame = @name
and sid <> 0×01 and sid is not null)
               Begin
                 
Insert into #parm Select spid from master.dbo.sysprocesses where
loginame = @name and sid <> 0×01 and sid is not null
                 
Select @spid = min(value) from #parm
                  While @spid is not null
                     Begin
                       
Select @cmd = ‘Kill ‘ + convert(varchar(5),@spid)
                       
Exec (@cmd)
                       
Select @spid = min(value) from #parm where value > @spid
                     End
               End

            — Not all kill commands succeed; check again
           
If exists (Select * from master.dbo.sysprocesses where loginame = @name
and sid <> 0×01 and sid is not null)
               Begin
                 
Print ‘Could not kill all active sessions for this login.’
                 
Print ‘Cannot drop the login at this time.’
               End
            Else
               Begin
                 
If charindex(‘\’, @name) > 0
                    
Exec sp_revokelogin @name
                  Else
                    
Exec sp_droplogin @name
               End
         End

   End
Else
   Begin
      Print ‘The login ”’ + @name + ”’ does not exist on SQL Server ”’ + @@servername + ”’.’
   End

GO