SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Remove SQL Logins from All Databases and SQL Server

By Rudy Panigas,

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 ***
-- Verion 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
EXEC sp_dropuser [TestUser1]

USE model
EXEC sp_dropuser [TestUser1]

USE ReportServer
EXEC sp_dropuser [TestUser1]

USE ReportServerTempDB
EXEC sp_dropuser [TestUser1]

-- ***********************************************************************************
-- *** 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 databas.

By using this method you can verify that the code generated is for the correct user and you can removed 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.



Total article views: 5062 | Views in the last 30 days: 7
Related Articles

database mirroring monitor - remove server or database

database mirroring monitor - remove server or database


SQL Server – Changing Ownership for All the User Databases to “sa” Account

Most people prefer to have “sa”  account as the database owner, primary reason being sa login cannot...


Delete All Database User Accounts for a Given Server Login

Easily and quickly delete all database accounts even if they don't have the same name as server logi...


removing principal from agent

remove principal from proxy account


SQL server accounts for development

SQL server accounts, design