Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
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 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.

Thanks,

Rudy

Total article views: 4780 | Views in the last 30 days: 20
 
Related Articles
FORUM

database mirroring monitor - remove server or database

database mirroring monitor - remove server or database

SCRIPT

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

FORUM

removing principal from agent

remove principal from proxy account

FORUM

SQL server accounts for development

SQL server accounts, design

SCRIPT

Changing database mail account SMTP servers

This will generate a script that drops and recreates existing db mail accounts and change the smpt s...

Tags
security    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones