use sybsystemprocs
go
sp_dropprocedure sp_disable_triggers
go
sp_dropprocedure sp_enable_triggers
go
sp_dropprocedure sp_enabletriggers
go
sp_dropprocedure sp_killdb
go
sp_dropprocedure sp_removelogin_user
go
sp_dropprocedure sp_removelogin
go

set quoted_identifier on
go

create procedure sp_disable_triggers as
   set ansinull                      on
   set close on endtran              on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   declare @cmdstring   varchar (255),
           @errorchar   varchar (  4),
           @tabname     varchar ( 30),
           @username    varchar ( 30)

   declare disabletriggers_cursor cursor for
      select name, user_name (uid)
      from sysobjects
      where (sysstat & 15) = 3
        and ((charindex ('sa_role', show_role ()) > 0)  or
             (uid = user_id ()))
        and ((((instrig <> 0)  and  ((sysstat2 & 1048576) <> 1048576))  or
              ((deltrig <> 0)  and  ((sysstat2 & 2097152) <> 2097152))  or
              ((updtrig <> 0)  and  ((sysstat2 & 4194304) <> 4194304))))
      order by name, user_name (uid)
   for read only

   open disabletriggers_cursor

   fetch disabletriggers_cursor into @tabname, @username

   if (@@sqlstatus = 2)
      begin
         print '    THERE ARE NO ENABLED TRIGGERS.'
      end
   else
      begin
         while (@@sqlstatus = 0)
            begin
               print ' '

               print '    DISABLING TRIGGERS FOR %1!.%2!.',  @username, @tabname

               select @cmdstring = 'alter table '
                                   +  @username
                                   +  '.'
                                   +  @tabname
                                   +  ' disable trigger'

               execute  (@cmdstring)

               fetch disabletriggers_cursor into @tabname, @username
            end

         if (@@sqlstatus <> 2)
            begin
               select @errorchar = convert (varchar (4), @@sqlstatus)

               print '  CURSOR ERROR.  STATUS = %1!.', @errorchar

               return 1
            end
      end

   close disabletriggers_cursor

   deallocate cursor disabletriggers_cursor
go

create procedure sp_enable_triggers as
   set ansinull                      on
   set close on endtran              on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   declare @cmdstring   varchar (255),
           @errorchar   varchar (  4),
           @tabname     varchar ( 30),
           @username    varchar ( 30)

   declare enabletriggers_cursor cursor for
      select name, user_name (uid)
      from sysobjects
      where (sysstat & 15) = 3
        and ((charindex ('sa_role', show_role ()) > 0)  or
             (uid = user_id ()))
        and (((sysstat2 & 1048576) = 1048576)  or
             ((sysstat2 & 2097152) = 2097152)  or
             ((sysstat2 & 4194304) = 4194304))
      order by name, user_name (uid)
   for read only

   open enabletriggers_cursor

   fetch enabletriggers_cursor into @tabname, @username

   if (@@sqlstatus = 2)
      begin
         print '    THERE ARE NO DISABLED TRIGGERS.'
      end
   else
      begin
         while (@@sqlstatus = 0)
            begin
               print ' '

               print '    ENABLING TRIGGERS FOR %1!.%2!.',  @username, @tabname

               select @cmdstring = 'alter table '
                                   +  @username
                                   +  '.'
                                   +  @tabname
                                   +  ' enable trigger'

               execute  (@cmdstring)

               fetch enabletriggers_cursor into @tabname, @username
            end

         if (@@sqlstatus <> 2)
            begin
               select @errorchar = convert (varchar (4), @@sqlstatus)

               print '  CURSOR ERROR.  STATUS = %1!.', @errorchar

               return 1
            end
      end

   close enabletriggers_cursor

   deallocate cursor enabletriggers_cursor
go

create procedure sp_enabletriggers (@DISABLE_FLAG  varchar (1) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    sp_enabletriggers CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   print ' '

   if (@DISABLE_FLAG IS NULL)
      begin
         execute  sp_enable_triggers
      end
   else
      begin
         execute  sp_disable_triggers
      end

   print ' '
go
sp_addcomment sp_enabletriggers, 'Enables all disabled triggers'
go
sp_addcomment sp_enabletriggers, '   if DISABLE_FLAG is non-null disable all enabled triggers'
go

create procedure sp_killdb (@DATABASE  varchar (30))  as
   set ansinull                      on
   set close on endtran              on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    sp_killdb CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @cmdstring   varchar (255),
           @errorchar   varchar (  4),
           @id          int,
           @login       varchar ( 30),
           @spid        smallint,
           @spidchar    varchar ( 11)

   print ' '

   declare killdb_cursor cursor for
      select spid, suser_name (suid)
      from master.dbo.sysprocesses
      where dbid  = db_id (@DATABASE)
        and dbid  > 1
        and spid <> @@spid
      order by spid
   for read only

   if (charindex ('sa_role', show_role ()) > 0)
      begin
         if (@DATABASE = 'master')
            begin
               print '    YOU CANNOT EXECUTE sp_killdb FOR DATABASE master.'

               print ' '

               return 1
            end
         else if (db_id (@DATABASE) IS NULL)
            begin
               print '    DATABASE %1! DOES NOT EXIST.', @DATABASE

               print ' '

               return 1
            end
         else
            begin
               open killdb_cursor

               fetch killdb_cursor into @spid, @login

               if (@@sqlstatus = 2)
                  begin
                     print '    THERE ARE NO ACTIVE PROCESSES FOR DATABASE %1!.', @DATABASE
                  end
               else
                  begin
                     print '    SPIDS WILL BE KILLED FOR DATABASE %1!.', @DATABASE

                     print ' '

                     while (@@sqlstatus = 0)
                        begin
                           select @spidchar = convert (varchar (11), @spid)

                           select @cmdstring = 'kill '  +  @spidchar

                           print '    KILLING SPID %1! FOR LOGIN %2!.',
                                      @spidchar, @login

                           execute  (@cmdstring)

                           fetch killdb_cursor into @spid, @login
                        end

                     print ' '

                     if (@@sqlstatus = 2)
                        begin
                           execute  sp_status  @PARM=@DATABASE

                           execute  sp_locking  @DATABASE=@DATABASE
                        end
                     else
                        begin
                           select @errorchar = convert (varchar (4), @@sqlstatus)

                           print '  CURSOR ERROR.  STATUS = %1!.', @errorchar

                           return 1
                        end
                  end

               close killdb_cursor

               deallocate cursor killdb_cursor
            end
      end
   else
      begin
         print '    ONLY AN ADMINISTRATOR CAN EXECUTE sp_killdb.'

         print ' '
      end
go
sp_addcomment sp_killdb, 'Kill all processes connected to DATABASE'
go

create procedure sp_removelogin_user (@SUIDCHAR  varchar (10))  as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   declare @arg1       varchar (30),
           @dbname     varchar (30),
           @suid       smallint,
           @username   varchar (30)

   select @dbname = db_name (),
          @suid   = convert (smallint, @SUIDCHAR)

   select @username = coalesce (name, '?')
   from sysusers
   where suid = @suid

   if ((exists (select *
                from sysalternates
                where suid = @suid))  or
       (exists (select *
                from sysusers
                where suid = @suid)))
      begin
         if ((exists (select o.id
                      from sysobjects o,
                           sysusers u
                      where u.suid = @suid
                        and o.uid  = u.uid))  or
             (exists (select m.error
                      from sysusermessages m,
                           sysusers u
                      where u.suid = @suid
                        and m.uid  = u.uid))  or
             (exists (select p.id
                      from sysobjects o,
                           sysprotects p,
                           sysusers u
                      where u.suid     = @suid
                        and p.grantor  = u.uid
                        and o.id       = p.id
                        and o.uid     <> u.uid))  or
             (exists (select t.usertype
                      from systypes t,
                           sysusers u
                      where u.suid     = @suid
                        and t.uid      = u.uid
                        and t.usertype > 99)))
            begin
               print '    %1! OWNS OBJECTS IN DATABASE %2!.',
                     @username, @dbname

               execute  sp_ownedby  @USER=@username, @DROP_FLAG=NULL

               print ' '

               if (exists (select *
                           from sysalternates
                           where altsuid = @suid))
                  begin
                     print ' '

                     print '    %1! IS ALIASED.', @username

                     print ' '

                     select ALIAS = suser_name (suid)
                     from sysalternates
                     where altsuid = @suid
                     order by ALIAS

                     print ' '
                  end
            end
         else if (exists (select *
                          from sysalternates
                          where suid = @suid))
            begin
               select @arg1 = suser_name (@suid)

               print '    DROPPING ALIAS %1! FROM DATABASE %2!.',
                     @arg1, @dbname

               print ' '

               execute  sp_dropalias  @loginame=@arg1
            end
         else if (exists (select *
                          from sysusers
                          where suid = @suid))
            begin
               if (exists (select *
                           from sysalternates
                           where altsuid = @suid))
                  begin
                     print '    %1! IS ALIASED.', @username

                     print ' '

                     select ALIAS = suser_name (suid)
                     from sysalternates
                     where altsuid = @suid
                     order by ALIAS

                     print ' '
                  end

               print '    DROPPING USER %1! FROM DATABASE %2!.',
                     @username, @dbname

               print ' '

               execute  sp_dropuser  @name_in_db=@username

               print ' '
            end
      end
go
sp_existsprocedure sp_removelogin_user
go

create procedure sp_removelogin (@LOGIN varchar (30))  as
   set ansinull                      on
   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    sp_removelogin CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   print ' '

   declare @retval     tinyint,
           @suidchar   varchar (10)

   select @retval = 0

   if ((charindex ('sa_role', show_role ()) = 0)  and  (charindex ('sso_role', show_role ()) = 0))
      begin
         print '    ONLY AN ADMINISTRATOR OR SECURITY OFFICER CAN EXECUTE sp_removelogin.'

         print ' '

         select @retval = 1
      end

   select @suidchar = convert (varchar (10), suid)
   from master.dbo.syslogins
   where name = @LOGIN

   if (@suidchar IS NULL)
      begin
         print '    LOGIN %1! DOES NOT EXIST.', @LOGIN

         print ' '

         select @retval = 1
      end

   if (exists (select *
               from master.dbo.sysdatabases
               where suid = suser_id (@LOGIN)))
      begin
         print '    LOGIN %1! OWNS DATABASES.', @LOGIN

         print ' '

         select @retval = 1
      end

    if (@retval = 0)
       begin
          execute  sp_globalexec  @UTILITY_SP='sp_removelogin_user',
                                  @ARG1=@suidchar

          execute  sp_droplogin  @loginame=@LOGIN
       end
    else
       begin
         print '    sp_removelogin EXECUTION TERMINATED.'

         print ' '
       end

   return @retval
go
sp_addcomment sp_removelogin, 'Removes a LOGIN from all databases and the server'
go
sp_procxmode sp_removelogin, anymode
go

update index statistics udescriptions
go
