Script to remove login which is mapped to several databases

• SueTons

  SSC Veteran

  Points: 287

  More actions

  June 19, 2012 at 2:50 pm

  #276582

  All,

  Please help or advise.

  I need a script to delete a login which is mapped(user mapping) to several databases.

  Does anyone have it? Or is there any other way that I can do this.

  Thanks in advance,

  SueTons

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 19, 2012 at 3:04 pm

  #1503117

  something like this should be pretty close:

  if you cannot drop a database user because it is the object owner, this would rroll it back so you can fix that first.

  SET XACT_ABORT ON

  BEGIN TRAN

  DECLARE @UserToDelete varchar(50);

  exec sp_msForEachDB '

  USE [?];

  IF EXISTS (SELECT * FROM [?].sys.database_principals WHERE name = N''myDomain\Lowell'')

  DROP USER [myDomain\Lowell];';

  IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'myDomain\Lowell')

  DROP LOGIN [myDomain\Lowell];

  COMMIT TRAN

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SueTons

  SSC Veteran

  Points: 287

  More actions

  June 19, 2012 at 3:11 pm

  #1503123

  Lowell (6/19/2012)

  ---

  something like this should be pretty close:

  if you cannot drop a database user because it is the object owner, this would rroll it back so you can fix that first.

  SET XACT_ABORT ON

  BEGIN TRAN

  DECLARE @UserToDelete varchar(50);

  exec sp_msForEachDB '

  USE [?];

  IF EXISTS (SELECT * FROM [?].sys.database_principals WHERE name = N''myDomain\Lowell'')

  DROP USER [myDomain\Lowell];';

  IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'myDomain\Lowell')

  DROP LOGIN [myDomain\Lowell];

  COMMIT TRAN

  Thanks Lowell for the quick reply. I will try it.

  SueTons.