H4CK3R 14/hacker14.iso/patches/sql/SQL70-KB815495-v7.00.1094-ENU.exe/hotfix1/Files/sp4_serv_uni.sql

Text (UTF-16) | 2003-05-29 | 57.8 KB | 969 lines

/*------------------------------------------------------------------------------

SP4_SERV.SQL

THIS SCRIPT TAKES THE SERVER-SIDE SYSTEM-PROCS FROM 7.0 SP3 to SP4.

Changes in this file are organized as follows (please maintain):
    System Tables (UPGRADE.SQL)
    System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL)
    Engine System Procs (U_TABLES.SQL / PROCSYST.SQL / ODSOLE.SQL)
    Schema Procs (OLEDBSCH.SQL / ANSIVIEW.SQL)
    ODBC/OLEDB Catalog Procs (INSTCAT.SQL)
    SEM SQLDMO System Procs (SQLDMO.SQL)

Changes to these scripts should NOT be placed in this file:
    Starfighter Procs (XPSTAR.SQL / INSTMSDB.SQL / SQLTRACE.SQL / WEB.SQL)
    Doc's Samples (INSTPUBS.SQL / INSTNWND.SQL)
    Replication Procs (REPLSYS.SQL / REPLCOM.SQL / REPLTRAN.SQL / REPLMERG.SQL)
These components will maintain separate upgrade scripts.

Notes:
+ Catalog-updates and sp_MS_upd_sysobj_category are enabled for the entire
    file.  Do not disable or re-enable them.  Please do not change set options.

------------------------------------------------------------------------------*/


--------------------------------------------------------------------------------
-- VERIFY Server is started in single-user-mode (catalog-updates enables), and
--    start marking of system-objects.
--------------------------------------------------------------------------------
execute sp_configure 'allow updates',1
go

reconfigure with override
go

exec sp_MS_upd_sysobj_category 1
go

--------------------------------------------------------------------------------
--    System Tables (UPGRADE.SQL)
--------------------------------------------------------------------------------
delete sysconfigures where config = 400
go

--------------------------------------------------------------------------------
--    System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL) English
--------------------------------------------------------------------------------
DELETE sysmessages
    where error in
    (
    1223
    )
go
insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
    (1223,13,0,'Process ID %d:%d cannot acquire lock "%hs" on resource %.*ls because a potential deadlock exists on Scheduler %d for the resource. Process ID %d:%d holds a lock "%hs" on this resource', 1033)
go

--------------------------------------------------------------------------------
--    System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL) Localized
--------------------------------------------------------------------------------



--------------------------------------------------------------------------------
--    Engine System views (Update ANSIVIEW per database)
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--    Engine System Procs (U_TABLES.SQL / PROCSYST.SQL / ODSOLE.SQL)
--------------------------------------------------------------------------------

------------------------------ sp_changedbowner -------------------------------
if object_id('sp_changedbowner') is not null
    drop proc sp_changedbowner
raiserror(15339,-1,-1,'sp_changedbowner')
go
create procedure sp_changedbowner
    @loginame       sysname,        -- login to become dbo
    @map            varchar(5) = NULL    -- True to map aliases, else drop
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @ret        int,
            @newsid     varbinary(85),
            @status     smallint

    -- CHECK PERMISSIONS (Note: All sysadmins are dbo) --
    if is_member('db_owner') = 0
    begin
        raiserror(15108,-1,-1)
        return(1)
    end

    -- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
    if db_name() in ('master', 'model', 'tempdb')
    begin
        raiserror(15109,-1,-1)
        return(1)
    end

    -- CHECK LOGIN NAME IS VALID (NT/SQL USER ONLY!) --
    select @newsid = sid, @status = 2 from master.dbo.syslogins
                    where loginname = @loginame and isntname = 0
    if @newsid is null
        select @status = 14, @newsid = get_sid('\U'+@loginame, NULL)
    if @newsid is null
    begin
        raiserror(15007,-1,-1,@loginame)
        return (1)
    end

    -- CHECK IF LOGIN ALREADY ALIASED IN DB --
    if exists (select sid from sysusers where isaliased = 1 and sid = @newsid)
    begin
        raiserror(15111,-1,-1)
        return (1)
    end

    -- CHECK IF LOGIN ALREADY KNOWN TO DATABASE --
    if exists (select sid from sysusers where sid = @newsid and uid <> 1)
    begin
        raiserror(15110,-1,-1)
        return (1)
    end

    -- MAKE THE FOLLOWING REMOVE/REMAP/DELETES ATOMIC --
    begin transaction

    -- REMAP DBO TO NEW SID --
    update sysusers set sid = @newsid, status = @status, updatedate = getdate()
            where name = 'dbo'

    -- REMOVE OTHER DBO-ALIASES IF REMAPPING NOT REQUESTED --
    if lower(@map) <> 'true'
    begin
        delete from sysusers where isaliased = 1 and altuid = user_id('dbo')
        raiserror(15500,-1,-1)
    end
    else
        raiserror(15499,-1,-1)     -- nothing to do to <remap>

    -- REFLECT NEW OWNER IN SYSDATABASES --
    update master.dbo.sysdatabases set sid = @newsid where dbid = db_id()
    commit transaction

    -- CHECKPOINT DATABASE TO FORCE CHANGES TO IN-MEMORY STRUCTURE --
    checkpoint
    raiserror(15501,-1,-1)
    return (0) -- sp_changedbowner
go

grant execute on sp_changedbowner to public
go

------------------------------ sp_dboption -------------------------------
if object_id('sp_dboption') is not null
    drop proc sp_dboption
go

raiserror(15339,-1,-1,'sp_dboption')
go
/*ANSI_NULLS ON  for creation of sp_dboption*/
set ansi_nulls on
go
create procedure sp_dboption  -- 1996/03/15 12:51
@dbname sysname = NULL,     /* database name to change */
@optname varchar(35) = NULL,  /* option name to turn on/off */
@optvalue varchar(10) = NULL  /* true or false */
as

set nocount    on

declare @dbid int         /* dbid of the database */
declare @dbsid varbinary(85)           /* id of the owner of the database */
declare @statvalue int     /* number of status option */
declare @statvalue2 int     /* number of status2 option */
declare @catvalue int     /* number of category option */
declare @optcount int      /* number of options like @optname */
declare @allstatopts int    /* bit map off all options stored in sysdatqabases.status
                            ** that can be set by sp_dboption. */
declare @alloptopts int    /* bit map off all options stored in sysdatqabases.status
                            ** that can be set by sp_dboption. */
declare @allcatopts int    /* bit map off all options stored in sysdatqabases.category
                            ** that can be set by sp_dboption. */
declare @exec_stmt nvarchar(550)
declare @devname sysname
declare @cur_dbname sysname
declare @ret_code int
declare @int1 int
,@opt_autoclose int
,@opt_trunclogonchkpt int
,@opt_tornpage int
,@fulloptname varchar(35)
,@orig_db_status int
,@orig_db_status2 int
,@opt_bit_singleuser integer
,@opt_catnull integer
,@opt_ansinulldefault integer
,@opt_defaulttolocalcursor integer
,@opt_selectintobulkcopy integer
,@opt_recursivetriggers integer
,@opt_offline integer
,@opt_quotedid integer
,@opt_cursorcommitclose integer
,@opt_compnull integer
,@opt_ansiwarnings integer
,@opt_autocrtstats integer
,@opt_autoupdstats integer
,@returncode    int

/*
**  If no @dbname given, just list the possible dboptions.
**  Only certain status bits may be set or cleared by sp_dboption.
*/

/*
** Get bitmap of all options that can be set by sp_dboption.
*/
select @allstatopts=number from master.dbo.spt_values where type = 'D'
   and name = 'ALL SETTABLE OPTIONS'

select @allcatopts=number from master.dbo.spt_values where type = 'DC'
   and name = 'ALL SETTABLE OPTIONS'

select @alloptopts=number from master.dbo.spt_values where type = 'D2'
   and name = 'ALL SETTABLE OPTIONS'

if @dbname is null
begin
   select 'Settable database options:' = name
      from master.dbo.spt_values
      where (type = 'D'
            and number & @allstatopts <> 0
            and number not in (0,@allstatopts))  /* Eliminate non-option entries */
         or (type = 'DC'
            and number & @allcatopts <> 0
            and number not in (0,@allcatopts))
         or (type = 'D2'
            and number & @alloptopts <> 0
            and number not in (0,@alloptopts))
      order by name
   return (0)
end

/*
**  Verify the database name and get info
*/
select @dbid = dbid, @dbsid = sid
    ,@orig_db_status = status
    ,@orig_db_status2 = status2
   from master.dbo.sysdatabases
      where name = @dbname

/*
**  If @dbname not found, say so and list the databases.
*/
if @dbid is null
   begin
      raiserror(15010,-1,-1,@dbname)
      print ' '
      select 'Available databases:' = name
         from master.dbo.sysdatabases
      return (1)
   end

/*
** If no option was supplied, display current settings.
*/
if @optname is null
   begin
      select 'The following options are set:' = v.name
         from master.dbo.spt_values v, master.dbo.sysdatabases d
            where d.name=@dbname
               and ((number & @allstatopts <> 0
                     and number not in (-1,@allstatopts)
                     and v.type = 'D'
                     and (v.number & d.status)=v.number)
                 or (number & @allcatopts <> 0
                     and number not in (-1,@allcatopts)
                     and v.type = 'DC'
                     and d.category & v.number <> 0)
                 or (number & @alloptopts <> 0
                     and number not in (-1,@alloptopts)
                     and v.type = 'D2'
                     and d.status2 & v.number <> 0))
      return(0)
   end

if lower(@optvalue) not in ('true', 'false', 'on', 'off') and @optvalue is not null
   begin
      raiserror(15241,-1,-1)
      return (1)
   end

/*
**  Use @optname and try to find the right option.
**  If there isn't just one, print appropriate diagnostics and return.
*/
select @optcount = count(*) ,@fulloptname = min(name)
      from master.dbo.spt_values
      where lower(name) like '%' + lower(@optname) + '%'
         and ((type = 'D'
              and number & @allstatopts <> 0
              and number not in (-1,@allstatopts))
          or (type = 'DC'
              and number & @allcatopts <> 0
              and number not in (-1,@allcatopts))
            or (type = 'D2'
              and number & @alloptopts <> 0
              and number not in (-1,@alloptopts)))

/*
**  If no option, show the user what the options are.
*/
if @optcount = 0
   begin
      raiserror(15011,-1,-1,@optname)
      print ' '

      select 'Settable database options:' = name
         from master.dbo.spt_values
         where (type = 'D'
               and number & @allstatopts <> 0
               and number not in (-1,@allstatopts))  /* Eliminate non-option entries */
            or (type = 'DC'
               and number & @allcatopts <> 0
               and number not in (-1,@allcatopts))
            or (type = 'D2'
               and number & @alloptopts <> 0
               and number not in (-1,@alloptopts))
         order by name

      return (1)
   end


/*
**  If more than one option like @optname, show the duplicates and return.
*/
if @optcount > 1
   begin
      raiserror(15242,-1,-1,@optname)
      print ' '

      select duplicate_options = name
         from master.dbo.spt_values
         where lower(name) like '%' + lower(@optname) + '%'
            and ((type = 'D'
                 and number & @allstatopts <> 0
                 and number not in (-1,@allstatopts))
              or (type = 'DC'
                 and number & @allcatopts <> 0
                 and number not in (-1,@allcatopts))
              or (type = 'D2'
                 and number & @alloptopts <> 0
                 and number not in (-1,@alloptopts))
                )
      return (1)
   end

/*
** read the status value to be set or the category value to be set
*/
select @statvalue = number
      from master.dbo.spt_values
      where lower(name) = lower(@fulloptname)
      and type = 'D'

select @statvalue2 = number
      from master.dbo.spt_values
      where lower(name) = lower(@fulloptname)
      and type = 'D2'

/*
** if a category option is being set, set the status option value to be 0
** so that we won't be inserting a null value to sysdatabases.status
*/
if @statvalue is null
      select @statvalue = 0

if @statvalue2 is null
      select @statvalue2 = 0

select @catvalue = number
      from master.dbo.spt_values
      where lower(name) = lower(@fulloptname)
      and type = 'DC'

    select @opt_autoclose = number from master.dbo.spt_values
        where type = 'D' and name = 'autoclose'

    select @opt_trunclogonchkpt = number from master.dbo.spt_values
        where type = 'D' and name = 'trunc. log on chkpt.'

    select @opt_tornpage = number from master.dbo.spt_values
        where type = 'D' and name = 'torn page detection'

    select @opt_catnull = number from master.dbo.spt_values
        where type = 'D2' and name = 'concat null yields null'

    select @opt_ansinulldefault = number from master.dbo.spt_values
        where type = 'D2' and name = 'ANSI null default'

    select @opt_defaulttolocalcursor = number from master.dbo.spt_values
        where type = 'D2' and name = 'default to local cursor'

    select @opt_selectintobulkcopy = number from master.dbo.spt_values
        where type = 'D' and name = 'select into/bulkcopy'

    select @opt_recursivetriggers = number from master.dbo.spt_values
        where type = 'D2' and name = 'recursive triggers'

    select @opt_offline = number from master.dbo.spt_values
        where type = 'D' and name = 'offline'

    select @opt_quotedid = number from master.dbo.spt_values
        where type = 'D2' and name = 'quoted identifier'

    select @opt_cursorcommitclose = number from master.dbo.spt_values
        where type = 'D2' and name = 'cursor close on commit'

    select @opt_compnull = number from master.dbo.spt_values
        where type = 'D2' and name = 'ANSI nulls'

    select @opt_ansiwarnings = number from master.dbo.spt_values
        where type = 'D2' and name = 'ANSI warnings'

    select @opt_autocrtstats = number from master.dbo.spt_values
        where type = 'D2' and name = 'auto create statistics'

    select @opt_autoupdstats = number from master.dbo.spt_values
        where type = 'D2' and name = 'auto update statistics'

if @statvalue > 0
begin

/*
**  You can not change any of the options in master, except the 'trunc log on
**  checkpoint' option (8).  If the user tries to do so tell them they can't.
*/
if (@dbid = db_id('master') and @statvalue <> @opt_trunclogonchkpt and @optvalue is not null)
   begin
      raiserror(15243,-1,-1,@optname)
      return (1)
   end

/* Check for allowable options on tempdb */
if (@dbid = db_id('tempdb')
    and @optvalue is not null)
    begin
        raiserror(15324,-1,-1,@optname, @dbname )
        return (1)
    end

/*Check for allowable option on Model and MSDB*/
if (@dbid in (db_id('model'), db_id('msdb')) and
    @statvalue = @opt_offline
    and @optvalue is not null)
    begin
        raiserror(15324,-1,-1,@optname, @dbname )
        return (1)
    end
end


if @statvalue2 > 0
begin
/* Check for allowable options on tempdb */
if (@dbid = db_id('tempdb') and not @statvalue2 in
    ( @opt_catnull, @opt_recursivetriggers
    ,@opt_ansinulldefault,@opt_defaulttolocalcursor
    ,@opt_quotedid, @opt_cursorcommitclose, @opt_compnull
  ,@opt_ansiwarnings, @opt_autocrtstats, @opt_autoupdstats)
    and @optvalue is not null)
    begin
        raiserror(15324,-1,-1,@optname, @dbname )
        return (1)
    end
end

/*
**  Only the SA or the dbo of @dbname can execute the update part
**  of this procedure so check.
*/
if (not (is_srvrolemember('sysadmin') = 1))
    and suser_sid() <> @dbsid and @optvalue is not null
    -- ALSO ALLOW db_owner ONLY IF DB REQUESTED IS CURRENT DB
    and (@dbid <> db_id() or is_member('db_owner') <> 1)
   begin
      raiserror(15244,-1,-1)
      return (1)
   end

/*
**  If we're in a transaction, disallow this since it might make recovery
**  impossible.
*/
set implicit_transactions off
if @@trancount > 0 and @optvalue is not null
   begin
      raiserror(15002,-1,-1,'sp_dboption')
      return (1)
   end


/*
**  Just want to see current setting of specified option.
*/
if @optvalue is null
begin
      select OptionName = v.name

            ,CurrentSetting =
               CASE
                  When ( ((v.number & d.status) = v.number
                          and v.type = 'D')
                      or (d.category & v.number <> 0
                           and v.type = 'DC')
                      or (d.status2 & v.number <> 0
                           and v.type = 'D2')
                       )
                     Then 'ON'
                  When NOT
                       ( ((v.number & d.status) = v.number
                          and v.type = 'D')
                      or (d.category & v.number <> 0
                           and v.type = 'DC')
                      or (d.status2 & v.number <> 0
                           and v.type = 'D2')
                       )
                     Then 'off'
               END

         from master.dbo.spt_values v, master.dbo.sysdatabases d
            where d.name=@dbname
               and ((v.number & @allstatopts <> 0
                     and v.number not in (-1,@allstatopts)   /* Eliminate non-option entries */
                     and v.type = 'D')
                 or (v.number & @allcatopts <> 0
                     and v.number not in (-1,@allcatopts)   /* Eliminate non-option entries */
                     and v.type = 'DC')
                 or (v.number & @alloptopts <> 0
                     and v.number not in (-1,@alloptopts)   /* Eliminate non-option entries */
                     and v.type = 'D2')
                   )
                and lower(v.name) = lower(@fulloptname)

   return (0)
end

---- Pre-detect 'single user' conflicts.
select @opt_bit_singleuser = number from master.dbo.spt_values
  where type='D  ' and name='single user'
if (@fulloptname           ='single user') --13834
   begin
   select @int1 = count(*) from master.dbo.sysprocesses
      where dbid = @dbid and spid<>@@spid
   if (@int1 > 0 and
    (@orig_db_status & @opt_bit_singleuser > 0 or
     lower(@optvalue) in ('true', 'on'))
      )
      begin
      raiserror(15089,-1,-1,@fulloptname)
      return (1)
      end
   end

/*
**  Now update sysdatabases.
*/
if lower(@optvalue) in ('true','on')
begin
   /*
   **  If this is the option to make the database offline
   **  we need to do some checking first to make sure no users.
   **  Unless it's the master db, no one can be using it.
   **  If it's the master db, only the SA may be using it.
   */
   if @statvalue in (512)
      and (select count(*) from master.dbo.sysprocesses
         where dbid = @dbid
         and sid <> 0x1) > 0
      begin
         raiserror(15069,-1,-1)
         return (1)
      end

   /*
   ** If we're setting the database 'offline' - use 'dbcc dbcontrol'
   ** rather than setting the status bit directly.
   */
   if @statvalue = 512  -- 512 ON means db is Offline.
   begin

      if (0 = (@statvalue & (select status from master.dbo.sysdatabases where name=@dbname)))
         begin  -- Currently db is Online.

         dbcc dbcontrol(@dbname,offline)
         if @@error>0
            begin
               raiserror(15245,-1,-1)
               return (1)
            end
         else
            raiserror(15327,-1,-1)
         end
      else
         raiserror(15328,-1,-1)

      /*
      ** Don't need to continue and set status bit or 'checkpoint'
      ** the database if option was 'offline' since the dbcc
      ** command does.
      */
      return(0)   /* Don't need to continue and 'checkpoint' db for this
            ** option. */
   end

   /*
   ** If we're setting the database  'read only', - use dbcc dbcontrol'
   ** rather than setting the status bit directly
   */
   if @statvalue = 1024 -- 1024 ON means db is Read only
   begin
      if (0 = (@statvalue & (select status from master.dbo.sysdatabases where name = @dbname)))
         begin  -- currently db is not readonly

         dbcc dbcontrol(@dbname, readonly)
         if @@error>0
            begin
               raiserror(15612, -1, -1)
               return (1)
            end
         else
            raiserror(15613, -1, -1)
         end
     else
         raiserror(15614, -1, -1)

     /*
     ** We are done with the readony option
     */
     return (0)

   end

   /*
   ** If we're setting the database 'single user', - use dbcc dbcontrol'
   ** rather than setting the status bit directly
   */
   if @statvalue = 4096 -- 4096 ON means db is single user
   begin
      if (0 = (@statvalue & (select status from master.dbo.sysdatabases where name = @dbname)))
         begin  -- currently db is not singleuser

         dbcc dbcontrol(@dbname, single)
         if @@error>0
            begin
               raiserror(15615, -1, -1)
               return (1)
            end
         else
            raiserror(15616, -1, -1)
         end
     else
         raiserror(15617, -1, -1)

     /*
     ** We are done with the readony option
     */
     return (0)

   end

   if @catvalue = 1   /* We're publishing the database. */
      begin
         select @exec_stmt = quotename(@dbname, '[')   + '.dbo.sp_replicationdboption'
          /* @dbname = N' +   quotename(@dbname, '''')  + ', @optname = ''publish'' , @value =  ''true'''*/

          exec @returncode = @exec_stmt @dbname,'publish','true'

        if @returncode = 0
        begin
            raiserror(15435,-1,-1)
            return(0)
        end
        else
            return (1)
      end

   if @catvalue = 2  /* Allowing subscriptions in the database. */
      begin
           update master.dbo.sysdatabases set category = category | 2
            where name = @dbname
         raiserror(15436,-1,-1)
         return (0)
      end

    if @catvalue = 4    /* Allowing merge publishing in the database. */
        begin
            select @exec_stmt = quotename(@dbname, '[')   + '.dbo.sp_replicationdboption'
            /*@dbname = N' +   quotename(@dbname, '''')  + ', @optname = ''merge publish'' , @value =  ''true'''*/
            exec @returncode = @exec_stmt @dbname,'merge publish','true'

            if @returncode = 0
            begin
                raiserror(15437,-1,-1)
                return(0)
            end
            else
                return (1)
        end

   /* All other options get handled identically. */
   update master.dbo.sysdatabases
        set status = status | @statvalue,
        status2 = status2 | @statvalue2
      where dbid = @dbid
end

/*
**  We want to set the requested option off.
*/

else

begin
   if @statvalue = 1024
        and (0 <> (2097152 & (select status from master.dbo.sysdatabases where dbid=@dbid)))
   begin
        /* The db is in standby mode (2097152 = 0x200000 = DBT_STANDBY)
           so disallow turning the read-only bit off */
        raiserror(15030,-1,-1)
        return (1)
   end

   if @statvalue = 512     /* We're bringing it online. */
   begin
        if (0 = (@statvalue & (select status from master.dbo.sysdatabases where name=@dbname)))
         -- Currently db is Online.
            raiserror(15438,-1,-1)
        else
        begin
            dbcc dbcontrol(@dbname,online)
            if @@error>0
            begin
                return (1)
            end
            else
            raiserror(15439,-1,-1)
        end

        return(0)
    end

   if @statvalue = 1024     /* We're turning off readonly. */
   begin
        if (0 = (@statvalue & (select status from master.dbo.sysdatabases where name=@dbname)))
         -- Currently db is not readonly.
            raiserror(15619,-1,-1)
        else
        begin
            dbcc dbcontrol(@dbname,readwrite)
            if @@error>0
            begin
                return (1)
            end
            else
            raiserror(15618,-1,-1)
        end

        return(0)
    end

   if @statvalue = 4096     /* We're turning off singleuser. */
   begin
        if (0 = (@statvalue & (select status from master.dbo.sysdatabases where name=@dbname)))
         -- Currently db is not singleuser.
            raiserror(15621,-1,-1)
        else
        begin
            dbcc dbcontrol(@dbname,multi)
            if @@error>0
            begin
                return (1)
            end
            else
            raiserror(15620,-1,-1)
        end

        return(0)
    end

    if @catvalue = 1   /* We're disabling publishing for the database. */
    begin
        select @exec_stmt = quotename(@dbname, '[')   + '.dbo.sp_replicationdboption'
        /* @dbname = N' +   quotename(@dbname, '''')  + ', @optname = ''publish'' , @value =  ''false'''*/

        exec @returncode = @exec_stmt @dbname, 'publish', 'true'

        if @returncode = 0
        begin
            raiserror(15440,-1,-1)
            return(0)
        end
        else
            return (1)

    end

    if @catvalue = 2  /* We're disabling subscriptions to database. */
    begin
        update master.dbo.sysdatabases set category = category & ~2
        where name = @dbname
         raiserror(15441,-1,-1)
        return (0)
    end

    if @catvalue = 4    /* Disable merge publishing in the database. */
        begin
         select @exec_stmt = quotename(@dbname, '[')   + '.dbo.sp_replicationdboption'
         /*@dbname = N' +   quotename(@dbname, '''')  + ', @optname = ''merge publish'' , @value =  ''false'''*/
         exec @returncode = @exec_stmt @dbname, 'merge publish', 'false'

        if @returncode = 0
        begin
            raiserror(15442,-1,-1)
            return(0)
        end
        else
            return (1)
        end

        /* All other options get handled identically. */
    update master.dbo.sysdatabases
        set status = status & ~@statvalue
        ,status2 = status2 & ~@statvalue2
        where dbid = @dbid
end

/*
**  CHECKPOINT the database that was changed.
*/
raiserror(15443,-1,-1)

select @exec_stmt = 'use ' +  quotename(@dbname, '[')   + ' checkpoint'
exec (@exec_stmt)
select @int1 = @@error
if (@int1 <> 0)
    begin
    update         master.dbo.sysdatabases
        set     status = @orig_db_status
             ,status2 = @orig_db_status2
        where     dbid   = @dbid
    end
else
    /*
    **flush the stored procedures in the database involved for recompile
    ** if changing compile time options
    */
    begin
        dbcc flushprocindb(@dbid)
    end


return (0) -- sp_dboption
go
/*ANSI_NULLS OFF after creation of sp_dboption*/
set ansi_nulls off
go

grant execute on sp_dboption to public
go

---------------------------- sp_MS_marksystemobject -----------------------------

if object_id('sp_MS_marksystemobject', 'P') IS NOT NULL
    drop procedure sp_MS_marksystemobject
raiserror('create procedure sp_MS_marksystemobject ...',0,1)
go
-- FOR INTERNAL USE ONLY ... DO NOT DOCUMENT --
-- This procedure sets a bit in sysobjects.  This bit has no meaning, various
--    groups (starfigther, davinci, replication) use it for different things
-- MSQL makes no warranty, express or implied, on what objects will or will
--    not have this bit set.  Use at your own risk.
--
create procedure sp_MS_marksystemobject
    @objname    nvarchar(517)  -- 517 is max for two part name
as
    -- pre-stuff --
    set nocount on

    -- CHECK PERMISSIONS (MUST BE DBO OR SETUP ADMIN) --
    if (not is_member('db_owner')=1)
    begin
        raiserror('sp_MS_marksystemobject: Must be db_owner',0,1)
        return 1
    end

    -- CHECK THE OBJECT NAME --
    if object_id(@objname, 'local') is null
    begin
        raiserror('sp_MS_marksystemobject: Invalid object name ''%ls''',0,1,@objname)
        return 1
    end

    -- CHECK THE OBJECT OWNER (MUST BE A SYSTEM USER) --
    if user_name(ObjectProperty(object_id(@objname, 'local'), 'ownerid'))
                not in ('dbo','INFORMATION_SCHEMA')
    begin
        raiserror('sp_MS_marksystemobject: Object must be owned by a system user.',0,1)
        return 1
    end

    -- DO THE UPDATE --
    begin tran
    dbcc LockObjectSchema(@objname)
    update sysobjects set status = status | 0xC0000000
                        where id = object_id(@objname, 'local')
    commit tran
    return @@error  -- sp_MS_marksystemobject
go
exec sp_MS_marksystemobject 'sp_MS_marksystemobject'
go
grant execute on sp_MS_marksystemobject to public
go


delete from spt_values where name = 'Cross DB Ownership Chaining' and number = 400 and type = 'C'
go


delete from spt_values where name = 'db chaining' and number = 0x400 and type = 'D2'
go

--------------------------------------------------------------------------------
--     SP_SETLOGIN USED BY AGENT
--------------------------------------------------------------------------------
if object_id('sp_setuserbylogin','X') IS NOT NULL
    exec sp_dropextendedproc 'sp_setuserbylogin'
execute sp_addextendedproc 'sp_setuserbylogin','(server internal)'
go
grant execute on sp_setuserbylogin to public
go
exec sp_MS_marksystemobject 'sp_setuserbylogin'
go
--------------------------------------------------------------------------------
--    End of SQLDMO System Procedures (SQLDMO.SQL)
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
-- END OF FILE: Turn off marking of system objects.
--    DO NOT ADD ANYTHING AFTER THIS POINT
--------------------------------------------------------------------------------
exec sp_MS_upd_sysobj_category 2
go

exec sp_configure 'allow updates',0
go

reconfigure with override
go