sp_configure ‘allow updates‘, 1RECONFIGURE WITH OVERRIDE

create procedure sp_droplogin@loginame sysnameas declare @exec_stmt nvarchar(890)     -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --set nocount ondeclare @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)endELSEbegin  dbcc auditevent (104, 2, 1, @loginame, NULL, NULL, NULL)end     -- DISALLOW USER TRANSACTION --set implicit_transactions offIF (@@trancount > 0)beginraiserror(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 = 0if (@sid is null)beginraiserror(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‘)beginraiserror(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)beginraiserror(15174, -1, -1, @loginame)select ‘Databases owned by login:‘ = name                from master.dbo.sysdatabases where sid = @sidreturn(1)end -- COLLECT ALL INSTANCES OF USE OF THIS LOGIN IN SYSUSERS --declare @dbname sysnamedeclare ms_crs_dbname cursor local keyset for select name from master.dbo.sysdatabasesopen ms_crs_dbnamefetch ms_crs_dbname into @dbnamewhile @@fetch_status >= 0beginif (has_dbaccess(@dbname) = 1)beginselect @exec_stmt = ‘use ‘ + quotename( @dbname , ‘[‘) + ‘  insert into #db_list (dbname, user_name)select N‘+ quotename( @dbname , ‘‘‘‘)+‘, name from sysuserswhere sid = suser_sid(N‘ + quotename( @loginame , ‘‘‘‘) + ‘) ‘exec (@exec_stmt)endelseraiserror(15622,-1,-1, @dbname) fetch ms_crs_dbname into @dbnameenddeallocate ms_crs_dbname     -- ERROR IF LOGIN USED AS USER IN ANY DATABASE --if (select count(*) from #db_list) <> 0beginraiserror(15175,-1,-1,@loginame)select‘Database name:‘ = dbname,‘User name:‘ = user_name,‘Mapping type:‘ = ‘user‘from #db_listorder by dbnamereturn (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 nullbegin        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)   endend     -- DELETE THIS LOGIN (ALSO DELETES REMOTE LOGINS MAPPED TO IT) --delete from master.dbo.sysxlogins where sid = @sid     -- FINALIZATION: SUCCESS/FAILURE MESSAGEif @@rowcount > 0begin-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --exec(‘use master grant all to null‘) raiserror(15479,-1,-1)return (0)endelsebeginraiserror(15007,10,-1,@loginame)return (1)end     -- sp_droplogin GO