Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Sql Server script to automate the removal of logins

Here is another one of those files that just floats around in a
SqlScripts directory on my computer. I’ve found this script useful many
times. This was written by Clinton Herring many moons ago. This script
will remove a login from Sql Server. What’s so fancy about the script?
Well, the script takes care of going through each database and removing
this login’s permissions and object ownership (at least as much as
possible) so you don’t have to spend time searching and searching for
this owner throughout all the databases on a server. You’ll want to
make note of the comments Clinton has left in the header, as there is
important information on the behavior and actions taken when a scenario
is encountered. One such scenario is: when a login you want to remove
owns a database, that database ownership is given to ‘sa’.

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)

      — 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

This entry was posted in Most Popular, Sql Development. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

12 Responses to Sql Server script to automate the removal of logins

  1. Marcelo says:

    Save a lot of boring work! Thank you!

  2. jyothi says:

    do you have the 2005 version for this remove login script

  3. Gilnei says:

    My compliments to Raymond. You did it all…

  4. Eric says:

    Thanks a lot for sharing this Raymond.

  5. Anonymous says:

    good code

  6. muttu says:

    good code!

  7. dfd says:

    sdfsdfsdfsd

  8. Santosh says:

    I tried to write the code,but was not able to do it.I used to get error when I did dynamic sql exec(use dbname),The person who wrote it is really genious.Thanks for sharing the code.

  9. CsOver says:

    good code!but this seem very hard to write it alonely!Saved it on my machine!

  10. darrell says:

    Yep, this one’s a keeper!

  11. Thanks a lot for sharing this Raymond. Really useful for me… bookmarked! :)

  12. shebert says:

    Nice code snippet. Saved! Thanks!

Leave a Reply