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