Drop Logins

• sravanthiyerramreddy86

  SSC Veteran

  Points: 254

  More actions

  February 3, 2011 at 5:03 am

  #234392

  Hi,

  can anyone post a script for droppping 100 logins at a time..

  Thanks in advance.

  Sravanthi

• Lowell

  SSC Guru

  Points: 323507

  More actions

  February 3, 2011 at 6:22 am

  #1281862

  well, you'd have to have the list of logins first...

  lets assume you want to drop all logins on a given server, and not just 100 specific ones? is it 100 SQL logins, Windows logins, or everything? what about the matching USERS in each database? will you be stranding them if they exist?

  what you want to use is the metadata to build a list of all the desired commands, and then execute that script.

  take a look at the results of this SQL: that lists all the logins and roles on the server...some of them, obviously, you cannot drop.

  select * from sys.server_principals

  the example below only generates the list...it's up to you to go over the results, make the query more or less restrictive, and then copy/paste the final acceptable pieces after that:

  /*--Results:

  EXEC sp_droplogin mydomain\lowell;

  EXEC sp_droplogin bob;

  EXEC sp_droplogin Noobie;

  EXEC sp_droplogin APPADMIN;

  EXEC sp_droplogin APPUSER;

  EXEC sp_droplogin APPREADER;

  */

  select 'EXEC sp_droplogin ' + name + ';' As TheCommand

  FROM sys.server_principals

  WHERE type_desc in('SQL_LOGIN','WINDOWS_LOGIN')

  AND name NOT IN

  ( SELECT 'sa' UNION ALL

  SELECT '##MS_PolicyTsqlExecutionLogin##' UNION ALL

  SELECT 'NT AUTHORITY\SYSTEM' UNION ALL

  SELECT 'NT AUTHORITY\NETWORK SERVICE' UNION ALL

  SELECT '##MS_PolicyEventProcessingLogin##'

  )

  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!