drop procedure sp_droplogin_all
go
create procedure sp_droplogin_all
(
@loginname varchar(30)
) as
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    30 Oct 1996                                             */
/*      Description:    This SP can be used to drop a login from all databases. */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
declare @dbname varchar(30), @userid varchar(10),
        @username varchar(30), @type varchar(5)
if suser_id() <> 1
begin
        raiserror(15003,-1,-1)
        return (1)
end
select @userid = convert(varchar, suser_id(@loginname))
if @userid is null
begin
        raiserror('Invalid login name.',-1,-1)
        return (1)
end
create table #mappings (dbname varchar(30), username varchar(30), type varchar(5))
declare dbinfo cursor for select name from sysdatabases
open dbinfo
while('FETCH IS OK' = 'FETCH IS OK')
begin
        fetch next from dbinfo into @dbname
        if @@fetch_status < 0 break
        exec('use ' + @dbname + ' insert #mappings 
                select db_name(), name, ''user'' from sysusers
                where suid = ' + @userid +
                ' union all select db_name(), user_name(altsuid), ''alias''
                from sysalternates
                where suid = ' + @userid)
end
close dbinfo
deallocate dbinfo

declare mappings cursor for select dbname , username, type from #mappings
open mappings
while('FETCH IS OK' = 'FETCH IS OK')
begin
        fetch next from mappings into @dbname, @username, @type
        if @@fetch_status < 0 break
        if @type = 'user'
                exec('use ' + @dbname + ' exec sp_dropuser ''' + @username + '''')
        else
                exec('use ' + @dbname + ' exec sp_dropalias ''' + @loginname + '''')
end
close mappings
deallocate mappings

exec sp_droplogin @loginname
go

This page was last updated on May 01, 2006 04:28 PM.