Drop multiple users on a single database

• Kevin Drysdale

  SSC-Addicted

  Points: 453

  More actions

  October 29, 2013 at 8:05 am

  #278641

  Good morning. We have a database that we need to keep around for historical purposes. I backed up and restored this database to another SQL Server 2005 server for this purpose. The thing is on the original server, this database had hundreds of users on it and their associated logins. On this 'new' server where I restored it, the logins do not exist - and I don't want to create the logins.

  There will only be about a dozen people that need to access this archived database, so I would like to drop all but these dozen or so users from this archived database. I suppose I could just leave them as orphaned, but would like to clean them up. Deleting manually is a nightmare, a script would be more efficient.

  I'm sure there's something out there, I just have been unable to find it during searches.

  Thank you!

• Matt Crowley

  SSCarpal Tunnel

  Points: 4163

  More actions

  October 29, 2013 at 11:10 am

  #1662464

  Assuming none of the users own schemas or objects, this will generate the commands for you. Edit as needed:

  select 'drop user [' + d.name + ']'

  from sys.database_principals d left join

  sys.server_principals s on d.sid = s.sid

  where d.type = 'U'

  and s.sid is null

• Kevin Drysdale

  SSC-Addicted

  Points: 453

  More actions

  October 29, 2013 at 11:19 am

  #1662467

  Matt - thank you.

  I will check this out shortly.