Hi,

please help on the below task.

Create a script to remove database access for the following user [SBI\xxx] from the dbs mentioned .The script should go through all the dbs and remove the user if exist.   Also generate an output indicating if the user was removed.

Thanks & Regards,

Muni

• Changed type Kalman TothModerator 2 hours 35 minutes ago

Blog: "Sql Server script to automate the removal of logins

Here is another one of those files that just floats around in a
 SqlScripts directory on my computer. Ive found this script useful many
 times. This was written by Clinton Herring many moons ago. This script
 will remove a login from Sql Server. Whats so fancy about the script?
 Well, the script takes care of going through each database and removing
 this logins permissions and object ownership (at least as much as
 possible) so you dont have to spend time searching and searching for
 this owner throughout all the databases on a server. Youll want to
 make note of the comments Clinton has left in the header, as there is
 important information on the behavior and actions taken when a scenario
 is encountered. One such scenario is: when a login you want to remove
 owns a database, that database ownership is given to sa."

LINK: http://codebetter.com/raymondlewallen/2005/05/13/sql-server-script-to-automate-the-removal-of-logins/

There is undocumented stored procedure that may help you

EXEC

sp_MSForeachDB"Use ? DROP USER test"

Assume that the name of SQL Login and Database Users are equal, I wrote a simple script for you. try this:

declare @user sysname;
declare @sql nvarchar(max);

set @user = 'test'
set @sql =  'declare @msg nvarchar(2048)'
select 
	@sql = @sql + char(13) +
'
if exists ( select 
				*
			from 
				' + quotename(name) + '.sys.database_principals 
			where 
				type in ( ''S'', ''U'' )
			  and 
				name = ''' + @user + ''' )
  begin
    begin try
	  set xact_abort on;
	  begin tran
	    drop user ' + quotename(@user) + ';
	    print ''This user eliminated from the database '' + ''' + quotename(name) + '''
	  commit tran
	end try
	begin catch
	if @@trancount > 0
	  rollback tran;
	  set @msg  = ''There is a problem with eliminating this user from the database '' + ''' + quotename(name) + ''' ;
	  throw 60000, @msg, 1;
	end catch
  end
'
from 
	sys.databases

exec sp_executesql @sql