If you need to recursively remove a user from all db's, run this.
What happens is the statement first checks whether the user is in the 'current' database (remember msforeachdb)
then, changes the current context to the database, executes the dropuser command, then prints that it did it.
DECLARE @usr sysname
DECLARE @passwd varchar(20)
SET @usr = 'AUser'
SET @passwd = 'password'
DECLARE @cmd1 nvarchar(2000)
SET @cmd1 = 'if exists (select name from ?.dbo.sysusers where upper(name) = '''+@usr+''') '+
'begin '+
'use ? '+
'exec sp_dropuser ''' +@usr+ ''''+
'print ''dropped user from ?'''+
'end'
exec sp_msforeachdb @cmd1
No comments:
Post a Comment