|
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 |