To Drop Login and it’s Users

Set nocount on
DECLARE @DebugOnly bit = 0; -- switch to 0 to actually drop the login and user(s); 1 displays the generated script.
DECLARE @cmd nvarchar(MAX);
DECLARE @ServerPrincipalName sysname;
DECLARE @DatabasePrincipalName sysname;

SET @ServerPrincipalName = N'loginname'; -- the name of the LOGIN to be dropped from the SERVER.
SET @DatabasePrincipalName = N'username'; -- the name of the USER to be dropped from each DATABASE.

SET @cmd = '';

SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'
UNION ALL
' END + N'SELECT ''' + d.name + N'''
 , sp.name COLLATE SQL_Latin1_General_CP1_CI_AS
 , dp.name COLLATE SQL_Latin1_General_CP1_CI_AS
FROM ' + QUOTENAME(d.name) + N'.sys.database_principals dp
 LEFT JOIN master.sys.server_principals sp ON dp.sid = sp.sid
WHERE sp.name = ''' + @ServerPrincipalName + N'''
 OR dp.name = ''' + @DatabasePrincipalName + N''''
FROM sys.databases d
WHERE d.state_desc = N'ONLINE';

SET @cmd = @cmd + N';'

DECLARE @Results TABLE (
 database_name SYSNAME NOT NULL
 , server_principal_name SYSNAME NULL
 , database_principal_name SYSNAME NULL
 , PRIMARY KEY CLUSTERED (database_name)
);

INSERT INTO @Results (database_name, server_principal_name, database_principal_name)
EXEC (@cmd);

SET @cmd = N'';

SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'
' END + N' USE ' + QUOTENAME(r.database_name) + N';
 PRINT (N''Dropping user ' + QUOTENAME(r.database_principal_name) + N' in ' + r.database_name + N''');

 SET @msg = N'''';

 SELECT @msg = @msg + CASE WHEN @msg = N'''' THEN N'''' ELSE CHAR(13) + CHAR(11) END + N''User [' + @DatabasePrincipalName + N'] owns '' + o.type_desc + N'' '' + QUOTENAME(s.name) + N''.'' + QUOTENAME(o.name) + N'' and cannot be dropped''
 FROM sys.objects o
 INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
 WHERE s.principal_id = (SELECT sp.principal_id FROM sys.database_principals sp WHERE sp.name = N''' + @DatabasePrincipalName + N''')
 PRINT (@msg);

 IF @msg <> N''''
 BEGIN
 SET @errCount = @errCount + 1;
 END
 ELSE
 BEGIN
 IF (
 SELECT (COUNT(1))
 FROM sys.schemas s
 WHERE s.principal_id = (SELECT sp.principal_id FROM sys.database_principals sp WHERE sp.name = N''' + r.database_principal_name + N''')
 ) > 0
 BEGIN
 PRINT (N''User ' + r.database_principal_name + N' owns a schema and cannot be dropped'');
 SET @errCount = @errCount + 1;
 END
 ELSE
 BEGIN
 DROP USER ' + QUOTENAME(r.database_principal_name) + ';
 END;
 END;
'
FROM @Results r;

SET @cmd = N'SET NOCOUNT ON;
DECLARE @msg nvarchar(MAX);
DECLARE @errCount int;
SET @errCount = 0;
BEGIN TRY
' + CASE WHEN @cmd = N'' THEN N' PRINT (N''User ' + @DatabasePrincipalName + N' does not exist in any database.'');' ELSE @cmd END + N'
END TRY
BEGIN CATCH
 PRINT (ERROR_MESSAGE());
 SET @errCount = @errCount + 1;
END CATCH

IF @errCount = 0
BEGIN
';

IF (
 SELECT COUNT(1)
 FROM @Results r
 WHERE r.server_principal_name IS NOT NULL
 ) > 0
BEGIN
 SELECT TOP(1) @cmd = @cmd + N' USE master;
 PRINT (N''Dropping LOGIN ' + QUOTENAME(r.database_principal_name) + N''');
 DROP LOGIN ' + QUOTENAME(r.server_principal_name) + N';'
 FROM @Results r;
END
ELSE
BEGIN
 SET @cmd = @cmd + N' PRINT (N''Login ' + @ServerPrincipalName + N' does not exist.'');';
END

SET @cmd = @cmd + N'
END
ELSE
BEGIN
 PRINT (N''Errors occurred dropping users; login has NOT been dropped'');
END';

IF @DebugOnly = 1
BEGIN
 PRINT @cmd
END
ELSE
BEGIN
 EXEC sys.sp_executesql @cmd;
END