Raymond Lewallen

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)

      -- 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 = 0x01 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 = ' +
                                '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