/*
 * This script removes all logins which name ends with '_msx_probe' including all
 * related user accounts in all databases. Normally the probe login has an
 * associated user in msdb only, but we need to check all other database to
 * cover the evenruality that a user has been created manually that maps to the
 * probe login, which would cause failure of the removal of the login.
 *
 * This script assumes user that runs it has admin rights.
 *
 * -MJW
 */

set nocount on

declare @cmd nvarchar(1000)
declare @dbname sysname
declare @loginame sysname
declare @username sysname

create table #db_list (dbname sysname collate database_default not null, user_name sysname collate database_default not null)

declare ms_crs_dbname cursor local keyset for select name from master.dbo.sysdatabases
declare ms_crs_logins cursor local keyset for select name from master.dbo.sysxlogins where name like '%_msx_probe'
declare ms_crs_list cursor for select * from #db_list

-- we need to be sure we can remove all users, hence admin rights
if (not is_srvrolemember('sysadmin') = 1)
begin
	raiserror(15247,-1,-1)
	return
end

-- see if this is an MSX; if not nothing to do
if (select count(*) from msdb.dbo.systargetservers) = 0
begin
	raiserror('This is not an MSX server - no action required', 10, 1)
	return
end

print 'Removing all MSX probe accounts...'

open ms_crs_logins
fetch next from ms_crs_logins into @loginame
while (@@fetch_status >= 0)
begin
	-- get login mapping for every database
	open ms_crs_dbname
	fetch ms_crs_dbname into @dbname
	while (@@fetch_status >= 0)
	begin
		select @cmd = 'use ' + quotename( @dbname , '[') + '
			insert into #db_list (dbname, user_name)
			select N'+ quotename( @dbname , '''')+', name from sysusers
			where sid = suser_sid(N' + quotename( @loginame , '''') + ') '
		exec (@cmd)
		fetch ms_crs_dbname into @dbname
	end
	close ms_crs_dbname

	-- Now process the list
	open ms_crs_list
	fetch ms_crs_list into @dbname, @username
	while (@@fetch_status >= 0)
	begin
		select @cmd = 'use ' + quotename( @dbname , '[') +
			' print ''Dropping user ' + @username + ' from ' + @dbname + '''' +
			' exec sp_dropuser ' + quotename( @username , '''')
		exec (@cmd)
		fetch ms_crs_list into @dbname, @username
	end
	close ms_crs_list

	-- clear mappings
	delete from #db_list

	print 'Dropping login ' + @loginame
	exec sp_droplogin @loginame = @loginame

	-- get next login
	fetch next from ms_crs_logins into @loginame
end

-- cleanup
deallocate ms_crs_logins
deallocate ms_crs_dbname
deallocate ms_crs_list

print 'Done.

Please note that in order to allow TSX servers
to connect, a new MSX account will have to be created
on this machine. In addition the TSX server needs to
be configured.

See the readme file for details how to do this.'