drop user on several databases

• EA

  Mr or Mrs. 500

  Points: 521

  More actions

  May 1, 2007 at 8:50 am

  #173237

  I want to drop users in several databases (but not all!). In pseudo code it's like:

  use database1

  go

  if exists (select name from sys.database_principals where upper(name) = 'name1todrop')

   drop user name1todrop

  go

  if exists (select name from sys.database_principals where upper(name) = 'name2todrop')

   drop user name2todrop

  go

  use database 2

  go

  if exists (select name from sys.database_principals where upper(name) = 'name1todrop')

   drop user name1todrop

  go

  .

  .

  This is working fine. But now I want to make a stored procedure out of it.

  I can't use the 'use database' (a USE database statement is not allowed in a procedure, function or trigger.)

  The drop user only works for the current database. But how to make it current in a stored procedure? The databasename is not allowed in the syntax....

   

  Any help or suggestions are welcome!

• DickBaker

  Old Hand

  Points: 334

  More actions

  May 1, 2007 at 9:29 am

  #703571

  you can perform queries on other db's on your same instance using a simple database prefix, and use dynamic-SQL to action the drop if required

  if

  exists (select name from database1.sys.database_principals where upper(name) = 'NAME1TODROP')

  exec('use database1; drop user NAME1TODROP')

  if

  exists (select name from database1.sys.database_principals where upper(name) = 'NAME2TODROP')

  exec('use database1; drop user NAME2TODROP')

  if

  exists (select name from database2.sys.database_principals where upper(name) = 'NAME1TODROP')

  exec('use database2; drop user NAME1TODROP')

  so you should be able to encapsulate this within a sproc.