Dropping all users

• sindhigrl

  Default port

  Points: 1448

  More actions

  April 28, 2008 at 11:39 am

  #189641

  I am creating a bunch of jobs that restore our prod dbs to our test and deve environments nightly. I'd like to include in that job a step that drops all users and their permissions, then the following step would grant only permissions to the users that need it on test and dev.

  My question is - Is there any way to drop all users without having to specify each user. I know I can say sp_dropuser 'Bob'

  sp_dropuser 'Tom'

  But I would rather just run one that would drop all. Is there anything I can use? Thanks!

  Thanks!

• SQL ORACLE

  One Orange Chip

  Points: 27807

  More actions

  April 28, 2008 at 2:38 pm

  #808046

  How about

  SELECT * FROM sys.sysusers WHERE uid>5 AND SUBSTRING(name,1,2)<>'db'

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  April 28, 2008 at 2:46 pm

  #808047

  I would recommend sp_revokedbaccess rather sp_dropuser

  ---------------------------------------------------------------------