sp_configure ‘allow updates‘, 1
RECONFIGURE WITH OVERRIDE

create procedure sp_droplogin
@loginame sysname
as

 
declare @exec_stmt nvarchar(890)

 
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @sid varbinary(85)

 
/*Create temp tables before any DML to ensure dynamic*/
    -- CREATE TEMPORARY TABLES FOR LATER USE --
    create table #db_list (dbname sysname collate database_default not null, user_name sysname collate database_default not null)
create table #retval (job_count int not null)

 
    -- CHECK PERMISSIONS --
IF (not is_srvrolemember(‘securityadmin‘) = 1)
begin
  dbcc auditevent (104, 2, 0, @loginame, NULL, NULL, NULL)
  raiserror(15247,-1,-1)
  return (1)
end
ELSE
begin
  dbcc auditevent (104, 2, 1, @loginame, NULL, NULL, NULL)
end

 
    -- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@@trancount > 0)
begin
raiserror(15002,-1,-1,‘sp_droplogin‘)
return (1)
end

 
    -- VALIDATE LOGIN NAME (SQL LOGIN) --
select @sid = sid from master.dbo.syslogins
        where loginname = @loginame and isntname = 0
if (@sid is null)
begin
raiserror(15007,10,-1,@loginame)
return(1)
end
    -- CANNOT CHANGE SA ROLES --
else if @sid = 0x1 -- ‘sa‘
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

 
-- CHECK IF @sid IS CURRENTLY LOGGED IN (ignore cached remote connections) --
if exists(select * from master.dbo.sysprocesses where sid = @sid and status != ‘dormant‘)
begin
raiserror(15434, -1, -1, @loginame)
return(1)
end

 
    -- CHECK IF ANY DATABASES ARE OWNED BY LOGIN --
if exists(select * from master.dbo.sysdatabases where sid = @sid)
begin
raiserror(15174, -1, -1, @loginame)
select ‘Databases owned by login:‘ = name
                from master.dbo.sysdatabases where sid = @sid
return(1)
end

 
-- COLLECT ALL INSTANCES OF USE OF THIS LOGIN IN SYSUSERS --
declare @dbname sysname
declare ms_crs_dbname cursor local keyset for select name from master.dbo.sysdatabases
open ms_crs_dbname
fetch ms_crs_dbname into @dbname
while @@fetch_status >= 0
begin
if (has_dbaccess(@dbname) = 1)
begin
select @exec_stmt = ‘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 (@exec_stmt)
end
else
raiserror(15622,-1,-1, @dbname)

 
fetch ms_crs_dbname into @dbname
end
deallocate ms_crs_dbname

 
    -- ERROR IF LOGIN USED AS USER IN ANY DATABASE --
if (select count(*) from #db_list) <> 0
begin
raiserror(15175,-1,-1,@loginame)
select
‘Database name:‘ = dbname,
‘User name:‘ = user_name,
‘Mapping type:‘ = ‘user‘
from #db_list
order by dbname
return (1)
end

 
    -- VERIFY NO JOBS IN MSDB OWNED BY THIS LOGIN --
if db_id(‘msdb‘) is not null
        and object_id(‘msdb.dbo.sp_check_for_owned_jobs‘) is not null
begin
        exec msdb.dbo.sp_check_for_owned_jobs @loginame, ‘#retval‘
   if exists (select job_count from #retval where job_count > 0)
   begin
   declare @job_count int
   select @job_count = job_count from #retval
   raiserror(14248, -1, -1, @job_count)
   return (1)
   end
end

 
    -- DELETE THIS LOGIN (ALSO DELETES REMOTE LOGINS MAPPED TO IT) --
delete from master.dbo.sysxlogins where sid = @sid

 
    -- FINALIZATION: SUCCESS/FAILURE MESSAGE
if @@rowcount > 0
begin
-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
exec(‘use master grant all to null‘)

 
raiserror(15479,-1,-1)
return (0)
end
else
begin
raiserror(15007,10,-1,@loginame)
return (1)
end     -- sp_droplogin

 
GO