Remove a user without leaving orphan data in SQL Server

Completely remove users from SQL Server without leaving orphan data

Context

Dropping a user from the logins section can create orphaned users on the databases, so it is advised for you to remove database access before then deleting the user. This can be quite time consuming, so armed with the various information I found I came up with the code below.

It has saved me time when removing users, so hopefully it may help you.

SQL

DECLARE @UserName SysName='dfgsfdg'--Add Domain if Windows User
SET NOCOUNT ON
CREATE TABLE #UserTable(UserName sysname,GroupName sysname,LoginName sysname NULL,DefDBName sysname NULL,DefSchemaName sysname NULL,UserID smallint,SID smallint)
CREATE TABLE #Databases(DATABASE_NAME sysname,DATABASE_SIZE INT, REMARKS varchar(254))
INSERT INTO #Databases EXEC sp_databases
 
DECLARE @DBName sysname
 
DECLARE c1 CURSOR FOR (SELECT DATABASE_NAME FROM #Databases)
open c1
fetch next from c1 into @DBName
WHILE @@FETCH_STATUS= 0
BEGIN
PRINT @DBName
EXEC ('USE'+ @DBName +' INSERT INTO #UserTable EXEC sp_helpuser')
  IF (SELECT COUNT(*)FROM #UserTable WHERE UserName=@UserName)>0
  BEGIN
  PRINT'Removing '''+@UserName +''' FROM '+@DBName
  EXEC ('USE '+ @DBName +' EXEC sp_dropuser '''+@UserName +'''')
  END
DELETE FROM #UserTable--ClearTable
fetch next from c1 into @DBName
END
CLOSE C1
deallocate c1
PRINT 'Revoking Login'
IF CHARINDEX(@UserName,'\')>BEGIN
EXEC ('EXEC sp_revokelogin '''+ @UserName +'''')--Check string for domain info, assume windows if '/' found
END
IF CHARINDEX(@UserName,'\')=AND EXISTS(SELECT * FROM sys.server_principals WHERE name =@UserName) BEGIN--Check if SQL Login
EXEC ('DROP LOGIN ['+ @UserName +']')
END
DROP TABLE #UserTable
DROP TABLE #Databases
Ousia Logo