Remove SQL Logins from All Databases and SQL Server

Have you ever had to delete a user from SQL server but are warned that the ID may still exist in a database? Or have you wanted to ensure that the account has been completed removed? Recently, a request came to me to remove many ids from several servers (SQL 2000 and SQL 2005) which via the EM/SSMS with would have taken some time. So I decided to make this job a little easier and more complete by writing a stored procedure that would help in this task.

The stored procedure will hunt for the user id specified in every database and then generate the T-SQL code used to actually delete the account. Below is an example.

Here I created a test id called "TestUser1"
To start, execute the code which will produce a stored procedure called: sp_dba_RemoveUser. Next supply the user account you want to delete and execute.
[sp_dba_RemoveUser] 'testuser1'

Finally the output is generated and you would copy and paste the results to the query window and execute.
Here is an example of the output generated.

-- *** Remove User/Login Tool ***
-- Version 1.2
--
-- This code will generate the t-sql code needed to remove a specific user from databases and
-- from SQL Server logins if needed.

-- ***********************************************************************************
-- *** Execute the following code to remove user from ALL DATABASES, if needed ***


USE master
GO
EXEC sp_dropuser [TestUser1]
GO

USE model
GO
EXEC sp_dropuser [TestUser1]
GO

USE ReportServer
GO
EXEC sp_dropuser [TestUser1]
GO

USE ReportServerTempDB
GO
EXEC sp_dropuser [TestUser1]
GO

-- ***********************************************************************************
-- *** Execute the following code to remove user from SQL Server login, if needed ***

IF EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = 'testuser1')
EXEC sp_droplogin testuser1

-- End of code

As you can see the T-SQL code goes to every database and drops the user's account. Once completed; it will drop the user from SQL server. Code is created if the user id exists in that database.

By using this method you can verify that the code generated is for the correct user and you can remove lines for database that you want to keep and/or you have to the option to delete the account for only the databases but keep the server login.

Hope this help you as it has helped me.

Thanks for reading