SQL: Remove / Delete Orphan Users

As discussed in the post SQL Delete/Drop a User from each Database, when you delete a Login from SQL Server it does not remove them from the databases they have explicit permissions on. If a user login is removed, and they are not removed from the database you would have orphan users with permission to the database. The stored procedure listed will remove (delete) orphan datatabase user logins.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  
create procedure [dbo].[usp_DeleteOrphanUsers]
AS
  
-- revoke orphan user
declare @cmd as varchar(1000)
declare @uname as sysname
  
DECLARE my_cur INSENSITIVE SCROLL CURSOR
FOR
    -- orphan user
    SELECT 
        u.name 
    FROM 
        master..syslogins l RIGHT JOIN sysusers u 
            ON (l.sid = u.sid)
    WHERE 
        (l.sid IS NULL) 
        AND (issqlrole <> 1) 
        AND (isapprole <> 1)
        AND (u.name NOT IN ('INFORMATION_SCHEMA', 'guest', 'sys', 'system_function_schema'))
    ORDER BY 
        u.name
  
OPEN my_cur
  
WHILE (1 = 1)
    BEGIN
        FETCH FROM my_cur INTO @uname
        IF @@fetch_status <> 0
            BREAK
        SET @cmd = 'exec sp_revokedbaccess ''' + rtrim(@uname) + ''''
        PRINT @cmd
        EXEC (@cmd) 
    END
 
DEALLOCATE my_cur
 
RETURN(0)

 

 

The procedure would be executed like the following:

 

USE [databasename] /* where databasename is the name of the database to run the procedure on */
EXEC usp_DeleteOrphanusers

 

SQL: Delete/Drop a User from each Database

Deleting a user from SQL server deletes them from the security system, however it does not remove them from databases they have ‘securables’ (permissions speficified) for. The following procedure will remove a user from each database and remove their account.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_DeleteUser]
	@username sysname = NULL
AS
 
-- declare @nam as sysname
DECLARE @cmd1 AS varchar(500)

IF @username is not null
	BEGIN
		SET @cmd1 = 'PRINT ''?'' USE ? DROP USER ' + @username
		EXEC sp_msforeachdb @command1= @cmd1
		EXEC ('DROP LOGIN ' + @username)
		RETURN(0)
	END
RETURN (1)
The stored procedure is called like:

 

EXEC sp_DeleteUser 'loginname' 
-- where loginname is the name of the account that you would like deleted