sp_RemoveLogin

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.
--              8) 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)

      -- Check for database ownership
      -- Here dynamic sql is required to use the 'Use command'.
      If exists(select * from sysdatabases where sid = @sid)
         Begin
            Select @dbname = min(name) from master.dbo.sysdatabases where name <> 'master' and sid = @sid
            While @dbname is not null and exists(select * from sysdatabases where sid = @sid)
               Begin
                  Select @cmd = 'use ' + @dbname + ' exec sp_changedbowner ''sa'''
                  Exec (@cmd)
                  Select @dbname = min(name) from master.dbo.sysdatabases where name <> 'master' and sid = @sid
               End
         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 = ' +
                                '0x01 where owner_sid = suser_sid(''''' + @name + ''''') ' +
                                'update sysjobs set owner_sid = 0x01 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 <> 0x01 and sid is not null)
               Begin
                  Insert into #parm Select spid from master.dbo.sysprocesses where loginame = @name and sid <> 0x01 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 <> 0x01 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