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

SP3_SERV.SQL

THIS SCRIPT TAKES THE SERVER-SIDE SYSTEM-PROCS FROM 8.0SP2 to SP3.

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)
--------------------------------------------------------------------------------

IF OBJECT_ID('sysprocesses') IS NOT NULL
	DROP TABLE sysprocesses
go
CREATE TABLE sysprocesses (     -- see ntdbms\storeng\dfs\startup\dataserv.c
    spid            smallint NOT NULL,
    kpid            smallint NOT NULL,
    blocked         smallint NOT NULL,
    waittype        binary(2) NOT NULL,
	waittime		int NOT NULL,
	lastwaittype	nchar(32) NOT NULL,
	waitresource	nchar(256) NOT NULL,
    dbid            smallint NOT NULL,
    uid             smallint NOT NULL,
    cpu             int NOT NULL,
    physical_io     bigint NOT NULL,
    memusage        int NOT NULL,
    login_time      datetime NOT NULL,
    last_batch      datetime NOT NULL,
    ecid            smallint NOT NULL,
	open_tran		smallint NOT NULL,
    status          nchar(30) NOT NULL,
    sid             binary(86) NOT NULL,
    hostname        nchar(128) NOT NULL,
    program_name    nchar(128) NOT NULL,
    hostprocess     nchar(8) NOT NULL,
    cmd             nchar(16) NOT NULL,
    nt_domain       nchar(128) NOT NULL,
    nt_username     nchar(128) NOT NULL,
    net_address     nchar(12) NOT NULL,
    net_library     nchar(12) NOT NULL,
    loginame        nchar(128) NOT NULL,
    context_info	binary(128) NOT NULL,
    sql_handle		binary(20) NOT NULL,
    stmt_start		int NOT NULL,
    stmt_end		int NOT NULL
   )
go
GRANT SELECT ON sysprocesses TO PUBLIC
go
if not exists (select * from sysconfigures where config = 400)
begin
INSERT sysconfigures (config, status, value, comment) VALUES ( 400,  1 ,     0, N'Cross DB Ownership Chaining' )
end
go

--------------------------------------------------------------------------------
--	System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL) English
--------------------------------------------------------------------------------
DELETE sysmessages
 WHERE error in (
     211
    ,569
    ,570
    ,1229
    ,1620
    ,1621
    ,1836
    ,3124
    ,4454
    ,4455
    ,5600
    ,5601
    ,14360
    ,14361
    ,14362
    ,14363
    ,14364
    ,17176
    ,17177
    ,17883
    ,17884
    ,18779
    ,19030
    ,19031
    ,19032
    ,19033
    ,19034
    ,20624
    ,20625
    ,21695
    )
go

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(211, 23, 1, N'Possible schema corruption. Run DBCC CHECKCATALOG.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(569, 16, 0, N'The handle passed to fn_get_sql was invalid.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(570, 16, 0, N'INSTEAD OF triggers do not support direct recursion. Trigger execution failed.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(1229, 10, 0, N'Process ID %d:%d owns resources that are blocking processes on Scheduler %d.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(1836, 10, 0, N'Could not create default data file because the name was too long.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(3124, 14, 0, N'Must be a System Administrator to perform LOAD with CONVERT65.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(4454, 16, 0, N'View ''%.*ls'' is not updatable because the %s statement contains a GROUP BY ALL clause.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(4455, 16, 0, N'Derived table ''%.*ls'' is not updatable because the %s statement contains a GROUP BY ALL clause.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(5600,16,0,'The Cross Database Chaining option cannot be set to the specified value on the specified database.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(14360, 16, 0, N'%s is already configured as TSX machine', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(14361, 16, 0, N'MSX server does not support mixed security mode', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(14362, 16, 0, N'The MSX server must be running the Standard or Enterprise edition of SQL Server', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(14363, 16, 0, N'The MSX server is not prepared for enlistments [there must be an operator named ''MSXOperator'' defined at the MSX]', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(14364, 16, 0, N'The TSX server is not currently enlisted', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(17176, 10, 128, N'This instance of SQL Server last reported using a process id of %1 at %2 (local) %3 (UTC).', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(17177, 10, 128, N'This instance of SQL Server has been using a process id of %1 since %2 (local) %3 (UTC).', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(17883, 10, 128, N'The Scheduler %1!ld! appears to be hung. SPID %2!ld!, ECID %3!ld!, UMS Context 0x%4!p!', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(17884, 10, 128, N'Potential deadlocks exist on all the schedulers', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(18779, 16, 0, N'%s: Number of delete and insert log records mismatch in bounded update, insert:%d, delete:%d, beginLSN:(%ls), endLSN(%ls)', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(19030, 10, 128, N'SQL Trace started.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(19031, 10, 128, N'SQL Trace stopped.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(19032, 10, 128, N'SQL Trace stopped due to server shutdown.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(19033, 10, 128, N'Server started with ''-f''. Auditing will not be started.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(19034, 16, 128, N'Cannot start C2 audit trace. SQL Server is shutting down.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(20624, 16, 0, N'Server user ''%s'' is not a valid user in database ''%s''. Add the user account into the database first.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(20625, 16, 0, N'Could not create the merge replication PAL database role for publication ''%s''.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(21695, 10, 0, N'The job name ''%s'' was not generated for this replication agent, delete the job manually when it is no longer in use.', 1033)

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

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

if exists (select * from sysobjects
	   where xtype = N'TF' and uid = USER_ID('system_function_schema') and
		name = 'fn_get_sql')
	drop function system_function_schema.fn_get_sql
go

raiserror(15339,-1,-1,'system_function_schema.fn_get_sql')
go
create function system_function_schema.fn_get_sql
	(
	@handle binary(20)
	)
returns @tab table(dbid SmallInt,
	objectid Int,
	number SmallInt,
	encrypted Bit NOT NULL,
	text Text)
as
begin
	insert @tab
	select * from OpenRowset(FnGetSql, @handle)

	return
end -- fn_get_sql
go

if object_id('sp_fulltext_catalog','P') IS NOT NULL
	drop procedure sp_fulltext_catalog

---------------------------- sp_fulltext_catalog ------------------------------

raiserror(15339,-1,-1,'sp_fulltext_catalog')
go
create proc sp_fulltext_catalog
	@ftcat		sysname,		-- full-text catalog name
	@action 	varchar(20),	-- create | drop | | rebuild | ...
	@path		nvarchar(101) = null	-- optional file path for create (max of 100 chars!!!)
as
	declare @objname sysname,
			@objid	int,
			@vc1	nvarchar(517),
			@tabname	nvarchar(517),
			@tabwarn int

	select @tabwarn = 0

	-- FULLTEXT MUST BE ACTIVE IN DATABASE --
	if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
	begin
		raiserror(15601,-1,-1)
		return 1
	end

	-- VALIDATE PARAMS --
	if @action is null
		OR @action not in ('create','drop','start_full','start_incremental','stop','rebuild')
		OR @ftcat is null OR len(@ftcat) = 0
		OR (@path is not null and @action <> 'create')
		OR (len(@path) > 100 )
	begin
		raiserror(15600,-1,-1,'sp_fulltext_catalog')
		return 1
	end

	-- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	if @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sp_fulltext_catalog')
		return 1
	end

	-- CHECK PERMISSIONS (must be a dbowner) --
	if (is_member('db_owner') = 0)
	begin
		raiserror(15247,-1,-1)
		return 1
	end

	-- CHECK DATABASE MODE (must not be read-only) --
	if DATABASEPROPERTY(db_name(), 'IsReadOnly') = 1
	begin
		raiserror(15635, -1, -1, 'sp_fulltext_catalog')
		return 1
	end

	-- CATALOG MUST EXIST IF NOT CREATING --
	declare @ftcatid smallint
	select @ftcatid = ftcatid from sysfulltextcatalogs where name = @ftcat
	if @action not in ('create', 'drop') and @ftcatid is null
	begin
		raiserror(7641,-1,-1,@ftcat)
		return 1
	end

	if @action = 'create'
	begin
		DBCC CALLFULLTEXT ( 1, @ftcat, @path )	-- FTCreateCatalog( @ftcat, @path )
		if @@error <> 0 
			return 1
	end

	if @action = 'drop'
	begin
		-- CANNOT DROP CATALOG IF USED --
		if exists (select * from sysobjects where ftcatid = @ftcatid)
		begin
			raiserror(15604,-1,-1, @ftcat)
			return 1
		end

		DBCC CALLFULLTEXT ( 2, @ftcat )	-- FTDropCatalog( @ftcat )
		if @@error <> 0
			return 1
	end

	if @action = 'start_full'
	begin
		-- ERROR IF DATABASE IS IN SINGLE USER MODE --
		if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
		begin
			raiserror(15636, -1, -1, @ftcat)
			return 1
		end

		begin tran
		-- MARK TABLES/URLs AS --
		declare ms_crs_ftind cursor static local for select id, name from sysobjects
					where ftcatid = @ftcatid
		open ms_crs_ftind
		fetch ms_crs_ftind into @objid, @tabname
		while @@fetch_status >= 0
		begin

			-- ERROR ON TABLE IF TABLE IS NOT ACTIVATED --
			if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
			begin
				raiserror(15630, -1, -1, @tabname)
				goto error_exit
			end

			-- SKIP TABLE IF CRAWL ALREADY IN PROGRESS --
			if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
			begin
				select @tabwarn = 1
				fetch ms_crs_ftind into @objid, @tabname
				continue
			end


			-- START FULL CRAWL
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
			if @@error <> 0	-- server raised an error
			begin
				-- server did an ex_raise - this is unreachable code
				goto error_exit
			end

			-- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
			delete sysfulltextnotify where tableid = @objid

			if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
			and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
			begin

				select @vc1 = quotename(user_name(OBJECTPROPERTY(@objid,'OwnerId'))) + '.'
							+ quotename(object_name(@objid))

				dbcc lockobjectschema(@vc1)

				update sysobjects set status = status & ~128 where id = @objid

			end

			fetch ms_crs_ftind into @objid, @tabname
		end
		deallocate ms_crs_ftind

		commit tran

	end

	if @action = 'start_incremental'
	begin
		-- ERROR IF DATABASE IS IN SINGLE USER MODE --
		if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
		begin
			raiserror(15636, -1, -1, @ftcat)
			return 1
		end
		begin tran

		-- MARK TABLES/URLs AS --
		declare ms_crs_ftind cursor static local for select id, name from sysobjects
					where ftcatid = @ftcatid
		open ms_crs_ftind
		fetch ms_crs_ftind into @objid, @tabname
		while @@fetch_status >= 0
		begin
			-- ERROR ON TABLE IF TABLE IS NOT ACTIVATED --
			if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
			begin
				raiserror(15630, -1, -1, @tabname)
				goto error_exit
			end

			-- SKIP TABLE IF CRAWL ALREADY IN PROGRESS --
			if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
			begin
				select @tabwarn = 1
				fetch ms_crs_ftind into @objid, @tabname
				continue
			end

			if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
			and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
			begin

				-- START A FULL POPULATION FOR THIS TABLE --
				DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
				if @@error <> 0
				begin
					-- server did an ex_raise - this is unreachable code --
					goto error_exit
				end

				select @vc1 = quotename(user_name(OBJECTPROPERTY(@objid,'OwnerId'))) + '.'
						+ quotename(object_name(@objid))

				dbcc lockobjectschema(@vc1)

				update sysobjects set status = status & ~128 where id = @objid

			end
			else
			begin
				-- START AN INCREMENTAL POPULATION FOR THIS TABLE --
				DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 1 )
				if @@error <> 0
				begin
					-- server did an ex_raise - this is unreachable code --
					goto error_exit
				end

			end

			-- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
			delete sysfulltextnotify where tableid = @objid

			fetch ms_crs_ftind into @objid, @tabname
		end
		deallocate ms_crs_ftind
		commit tran


	end

	if @action = 'stop'
	begin
		declare ms_crs_ftind cursor static local for select id, name from sysobjects
					where ftcatid = @ftcatid
		open ms_crs_ftind
		fetch ms_crs_ftind into @objid, @tabname
		while @@fetch_status >= 0
		begin

			-- ERROR ON TABLE IF TABLE IS NOT ACTIVATED --
			if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
			begin
				raiserror(15630, -1, -1, @tabname)
				return 1
			end

			-- SKIP TABLE IF CRAWL ALREADY STOPPED - NO WARNING --
			if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 0)
			begin
				fetch ms_crs_ftind into @objid, @tabname
				continue
			end

			-- ERROR IF POPULATE STATUS OF THE TABLE IS CRAWLING AND CT ON
			if (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1)
			and ((ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 1)
			or (ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 2))
			begin
				raiserror(15642,-1,-1, @tabname)
				return 1
			end

			-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
			if @@error <> 0
			begin
				-- server did an ex_raise - this is unreachable code --
				return 1
			end

			fetch ms_crs_ftind into @objid, @tabname
		end
		deallocate ms_crs_ftind

	end

	if @action = 'rebuild'
	begin

		-- RE-CREATE CATALOG (Will first drop)
		select @path = path from sysfulltextcatalogs where ftcatid = @ftcatid
		DBCC CALLFULLTEXT ( 16, @ftcat, @path )	-- FTCreateCatalog( @ftcat, @path )
		if @@error <> 0
		begin 
			-- server did an ex_raise - this is unreachable code --
			return 1
		end

		begin tran

		-- RE-ACTIVATE TABLES/URLs --
		declare ms_crs_ftind cursor static local for select id from sysobjects
					where ftcatid = @ftcatid
		open ms_crs_ftind
		fetch ms_crs_ftind into @objid
		while @@fetch_status >= 0
		begin
			DBCC CALLFULLTEXT ( 5, @ftcatid, @objid )	-- FTAddURL( @ftcat, db_id(), @objid )
			if @@error <> 0
			begin
				-- server did an ex_raise - this is unreachable code --
				goto error_exit
			end

			-- CHECK TABLE FOR NOTIFICATIONS --
			if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
				and ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
			begin
				-- ERROR IF DATABASE IS IN SINGLE USER MODE --
				if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
				begin
					select @objname = object_name(@objid)
					raiserror(15638, -1, -1, @objname)

					select @vc1 = quotename(user_name(OBJECTPROPERTY(@objid,'OwnerId'))) + '.'
						+ quotename(@objname)


					dbcc lockobjectschema(@vc1)

					-- DISABLE FULLTEXT AUTO PROPAGATION (NO ERROR IF ALREADY DISABLED AND --
					-- IGNORE ANY OTHER ERRORS) --
					DBCC CALLFULLTEXT ( 9, @objid )	-- FTDisableNotify( db_id(), @objid )
					if @@error <> 0
					begin
						-- server did an ex_raise - this is unreachable code --
						goto error_exit
					end

					-- TURN OFF CHANGE TRACKING ACTIVE BITS IN SYSOBJECTS --
					update sysobjects set status = status & ~192 where id = @objid

					fetch ms_crs_ftind into @objid
					continue
				end

				-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
				DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
				if @@error <> 0
				begin
					-- server did an ex_raise - this is unreachable code --
					goto error_exit
				end

				-- START A FULL CRAWL FOR THIS TABLE --
				DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
				if @@error <> 0
				begin
					-- server did an ex_raise - this is unreachable code --
					goto error_exit
				end

				-- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
				delete sysfulltextnotify where tableid = @objid

			end

			-- CHECK TABLE FOR AUTOPROPAGATION  -
			if ObjectProperty(@objid, 'TableFulltextAutoPropagationOn') = 1
				and ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
			begin
				DBCC CALLFULLTEXT ( 10, @ftcatid, @objid )	-- FTEnableAutoProp( @ftcatid, db_id(), @objid )
				if @@error <> 0
				begin
					-- server did an ex_raise - this is unreachable code --
					goto error_exit
				end
			end

			fetch ms_crs_ftind into @objid
		end
		deallocate ms_crs_ftind

		commit tran

	end
	if(@tabwarn <> 0)
	begin
		raiserror(15643, -1, -1)
		return 0
	end
	return 0	-- sp_fulltext_catalog

error_exit:
	-- 'stop', 'rebuild' never get here, this is only for 'start_full', 'start_incr'
	-- here we commit the changes for all tables on which the operation succeeded.  
	-- Before 'goto error_exit' is called, schema changes made to table currently 
	-- under cursor must be undone (so far, no schema changes)
	commit tran
	return 1	-- sp_fulltext_catalog

go

grant execute on sp_fulltext_catalog to public
go


--------------------------------------------------------------------------------
-- sp_change_users_login
--------------------------------------------------------------------------------
if object_id('sp_change_users_login','P') IS NOT NULL
    drop procedure sp_change_users_login
go

raiserror(15339,-1,-1,'sp_change_users_login')
go
CREATE PROCEDURE sp_change_users_login
    @Action               varchar(10)       -- REPORT / UPDATE_ONE / AUTO_FIX
   ,@UserNamePattern      sysname  = Null
   ,@LoginName            sysname  = Null
   ,@Password			  sysname  = Null
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @exec_stmt nvarchar(430)

	declare @ret            int,
            @FixMode        char(5),
            @cfixesupdate   int,        -- count of fixes by update
            @cfixesaddlogin int,        -- count of fixes by sp_addlogin
            @dbname         sysname,
            @loginsid       varbinary(85),
            @110name        sysname

    -- SET INITIAL VALUES --
    select  @dbname         = db_name(),
            @cfixesupdate   = 0,
            @cfixesaddlogin = 0

    -- ERROR IF IN USER TRANSACTION --
    if @@trancount > 0
    begin
        raiserror(15289,-1,-1)
        return (1)
    end

    -- INVALIDATE USE OF SPECIAL LOGIN/USER NAMES --
    if suser_sid(@LoginName) = 0x1	-- 'sa'
    begin
        raiserror(15287,-1,-1,@LoginName)
        return (1)
    end
    if user_id(@UserNamePattern) in (1,0,3,4) --dbo, public, INFORMATION_SCHEMA, system_function_schema
    begin
        raiserror(15287,-1,-1,@UserNamePattern)
        return (1)
    end

    -- HANDLE REPORT --
    if upper(@Action) = 'REPORT'
    begin

        -- VALIDATE PARAMS --
        if @UserNamePattern IS NOT Null or @LoginName IS NOT Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- GENERATE REPORT --
        select UserName = name, UserSID = sid from sysusers
            where issqluser = 1 and (sid is not null and sid <> 0x0)
                    and suser_sname(sid) is null
            order by name
        return (0)
    end

    -- HANDLE UPDATE_ONE --
    if upper(@Action) = 'UPDATE_ONE'
    begin

        -- CHECK PERMISSIONS --
        if not is_member('db_owner') = 1
        begin
            raiserror(15247,-1,-1)
            return (1)
        end

        -- ERROR IF PARAMS NULL --
        if @UserNamePattern IS Null or @LoginName IS Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- VALIDATE PARAMS --
        -- Can ONLY remap SQL Users to SQL Logins!  Should be no need
        --  for re-mapping NT logins, and if you try, you'll mess up
        --  the user status bits! 
        if not exists (select name from sysusers where
                name = @UserNamePattern             -- match user name
            and issqluser = 1)                      -- must be sql user
        begin
            raiserror(15291,-1,-1,'User',@UserNamePattern)
            return (1)
        end
        select @loginsid = sid from master.dbo.syslogins where
                loginname = @LoginName              -- match login name
            and isntname = 0                        -- cannot use nt logins
        if @loginsid is null
        begin
            raiserror(15291,-1,-1,'Login',@LoginName)
            return (1)
        end

        -- ERROR IF SID ALREADY IN USE IN DATABASE --
        if exists (select sid from sysusers where sid = @loginsid
                    and name <> @UserNamePattern)
        begin
		    raiserror(15063,-1,-1)
		    return (1)
        end

        -- CHANGE THE USERS LOGIN (SID) --
        update sysusers set sid = @loginsid, updatedate = getdate()
                where name = @UserNamePattern and issqluser = 1
                and sid <> @loginsid

        -- FINALIZATION: REPORT (ONLY IF NOT SUCCESSFUL) AND EXIT --
        if @@error <> 0 or @@rowcount <> 1
            raiserror(15295,-1,-1, 0)
        return (0)
    end

    -- ERROR IF NOT AUTO_FIX --
    if upper(@Action) <> 'AUTO_FIX'
    begin
        raiserror(15286,-1,-1,@Action)
        return (1)
    end

    -- HANDLE AUTO_FIX --
    -- CHECK PERMISSIONS --
    if not is_srvrolemember('sysadmin') = 1
    begin
        raiserror(15247,-1,-1)
        return (1)
    end

    -- VALIDATE PARAMS --
    if @UserNamePattern IS Null or @LoginName IS NOT Null
    begin
        raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
        return (1)
    end

    -- LOOP THRU ORPHANED USERS --
	select @exec_stmt = 'DECLARE ms_crs_110_Users cursor global for
            select name from sysusers
            where name = N' + quotename( @UserNamePattern , '''')+ '
                and issqluser = 1 and suser_sname(sid) is null'
    EXECUTE (@exec_stmt)
    OPEN ms_crs_110_Users

    WHILE (110=110)
    begin
        FETCH next from ms_crs_110_Users into @110name
        if (@@fetch_status <> 0)
        begin
            DEALLOCATE ms_crs_110_Users
            BREAK
        end

        -- IS NAME ALREADY IN USE? --
        -- if suser_sid(@110name) is null
		if not exists(select * from master.dbo.syslogins where loginname = @110name)
        begin

		   -- VALIDATE PARAMS --
			if @Password IS Null
			begin
				raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
				return (1)
			end

            -- ADD LOGIN --
            execute @ret = sp_addlogin @110name, @Password, @dbname
            if @ret <> 0 or suser_sid(@110name) is null
            begin
                raiserror(15497,16,1,@110name)
                deallocate ms_crs_110_Users
                return (1)
            end
            select @FixMode = '1AddL'
            raiserror(15293,-1,-1,@110name)
        end
        ELSE
        begin
            Select @FixMode = '2UpdU'
            Raiserror(15292,-1,-1,@110name)
        end

        -- REPORT ERROR & CONTINUE IF DUPLICATE SID IN DB --
        select @loginsid = suser_sid(@110name)
        if user_sid(@loginsid) is not null
        begin
            raiserror(15331,-1,-1,@110name)
            CONTINUE
        end

        -- UPDATE SYSUSERS ROW --
        update sysusers set sid = @loginsid, updatedate = getdate(), status = (status & ~1) | 2 where name = @110name
        if @@error <> 0
        begin
            raiserror(15498,17,127)
            deallocate ms_crs_110_Users
            return (1)
        end


        if @FixMode = '1AddL'
            Select @cfixesaddlogin = @cfixesaddlogin + 1
        else
            Select @cfixesupdate = @cfixesupdate + 1
    end -- loop 110

    -- REPORT AND RETURN SUCCESS --
    raiserror(15295,-1,-1,@cfixesupdate)
    raiserror(15294,-1,-1,@cfixesaddlogin)
    return (0) -- sp_change_users_login
go


grant execute on sp_change_users_login to public
go
--------------------------------------------------------------------------------

------------------------------ sp_changedbowner -------------------------------
if object_id('sp_changedbowner','P') IS NOT NULL
    drop procedure sp_changedbowner
go

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) --
	-- See Bug Shiloh 362190 ---
    if not (is_srvrolemember('sysadmin') = 1)
    begin
        raiserror(15247,-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)
	grant all to null
    return (0) -- sp_changedbowner
go

grant execute on sp_changedbowner to public
go
--------------------------------------------------------------------------

delete from spt_values where name = 'CrossDbOwnChain' and number = 158 and type = 'T'
go
insert spt_values(name, number, type)
  values ('CrossDbOwnChain', 158, 'T')
go

delete from spt_values where name = 'Cross DB Ownership Chaining' and number = 400 and type = 'C'
go
insert spt_values(name, number, type, low, high,status)
	values ('Cross DB Ownership Chaining', 400, 'C', 0, 1, 0)
go

delete from spt_values where name = 'db chaining' and number = 0x400 and type = 'D2'
go
insert spt_values (name, number, type)
	values ('db chaining', 0x400, 'D2')
go

delete from spt_values where name = 'ALL SETTABLE OPTIONS' and type = 'D2'
go
insert spt_values (name, number, type)
	values ('ALL SETTABLE OPTIONS', 1469267968|0x800|0x1000|0x2000|0x400, 'D2')
go

delete from spt_values where name = 'ALL SETTABLE OPTIONS' and type = 'D'
go
insert spt_values (name, number, type)
	values ('ALL SETTABLE OPTIONS', 4202013, 'D')
go

------------------------------ sp_dboption -------------------------------
if object_id('sp_dboption','P') IS NOT NULL
	drop procedure 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  -- 1999/08/09 18:25
@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 @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 @fulloptname varchar(35)
declare @alt_optname varchar(50)
declare @alt_optvalue varchar(30)

declare @status 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
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


/*
**  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


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

/* if setting replication option, call sp_replicationdboption directly */
if (@catvalue <> 0)
	begin
		if lower(@optvalue) in ('true', 'on')
			begin
				select @alt_optvalue = 'true'
			end
		else
			begin
				select @alt_optvalue = 'false'
			end

		select alt_optname = quotename(@fulloptname, '''')
		select @exec_stmt = quotename(@dbname, '[')   + '.dbo.sp_replicationdboption'

		if @catvalue = 1
			begin
				select @alt_optname  = 'publish'
			end
		if @catvalue = 2
			begin
				select @alt_optname  = 'subscribe'
			end
		if @catvalue = 4
			begin
				select @alt_optname  = 'merge publish'
			end

		exec @exec_stmt @dbname, @alt_optname, @alt_optvalue
		return (0)
	end


/* call Alter Database to set options */

/* set option value in alter database*/
if lower(@optvalue) in ('true', 'on')
   begin
		select @alt_optvalue = 'ON'
   end

else
	begin
		select @alt_optvalue = 'OFF'
	end

/* if Cross DB Ownership Chaining is the option. Set it and get out */
if lower(@fulloptname) = 'db chaining'
	begin
	
    -- CHECK PERMISSIONS (Note: All sysadmins are dbo) --
    if not (is_srvrolemember('sysadmin') = 1)
    begin
        raiserror(15247,-1,-1)
        return(1)
    end

    -- CANT SET IN ANY OF OF MASTER/MODEL/TEMPDB --
    if lower(@dbname) in ('master', 'model', 'tempdb') or
	   (lower(@dbname) ='msdb' and @alt_optvalue = 'OFF')
    begin
        raiserror(5600,-1,-1)
        return(1)
    end

    -- ERROR IF IN USER TRANSACTION --
    if @@trancount > 0
    begin
        raiserror(15289,-1,-1)
        return (1)
    end

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

	select @status = status2 from master.dbo.sysdatabases where name = @dbname
	if @alt_optvalue = 'ON'
		set @status = @status | 1024
	else
		select @status = @status & (~ 1024)
	update master.dbo.sysdatabases set status2 = @status where name = @dbname


    -- REFLECT NEW STATUS IN SYSDATABASES --
    commit transaction

    -- CHECKPOINT DATABASE TO FORCE CHANGES TO IN-MEMORY STRUCTURE --
	select @exec_stmt = 'use ' +  quotename(@dbname, '[')   + ' checkpoint'		 
	exec (@exec_stmt)        

    return (0) -- end of set db chaining/user info in doubt  option
	end


/* set option name in alter database */
if lower(@fulloptname) = 'auto create statistics'
	begin
		select @alt_optname = 'AUTO_CREATE_STATISTICS'
	end

if lower(@fulloptname) = 'auto update statistics'
	begin
		select @alt_optname = 'AUTO_UPDATE_STATISTICS'
	end

if lower(@fulloptname) = 'autoclose'
	begin
		select @alt_optname = 'AUTO_CLOSE'
	end

if lower(@fulloptname) = 'autoshrink'
	begin
		select @alt_optname = 'AUTO_SHRINK'
	end

if lower(@fulloptname) = 'ansi padding'
	begin
		select @alt_optname = 'ANSI_PADDING'
	end

if lower(@fulloptname) = 'arithabort'
	begin
		select @alt_optname = 'ARITHABORT'
	end

if lower(@fulloptname) = 'numeric roundabort'
	begin
		select @alt_optname = 'NUMERIC_ROUNDABORT'
	end

if lower(@fulloptname) = 'ansi null default'
	begin
		select @alt_optname = 'ANSI_NULL_DEFAULT'
	end

if lower(@fulloptname) = 'ansi nulls'
	begin
		select @alt_optname = 'ANSI_NULLS'
	end

if lower(@fulloptname) = 'ansi warnings'
	begin
		select @alt_optname = 'ANSI_WARNINGS'
	end

if lower(@fulloptname) = 'concat null yields null'
	begin
		select @alt_optname = 'CONCAT_NULL_YIELDS_NULL'
	end

if lower(@fulloptname) = 'cursor close on commit'
	begin
		select @alt_optname = 'CURSOR_CLOSE_ON_COMMIT'
	end

if lower(@fulloptname) = 'torn page detection'
	begin
		select @alt_optname = 'TORN_PAGE_DETECTION'
	end

if lower(@fulloptname) = 'quoted identifier'
	begin
		select @alt_optname = 'QUOTED_IDENTIFIER'
	end

if lower(@fulloptname) = 'recursive triggers'
	begin
		select @alt_optname = 'RECURSIVE_TRIGGERS'
	end

if lower(@fulloptname) = 'default to local cursor'
	begin
		select @alt_optname = 'CURSOR_DEFAULT'

		if @alt_optvalue = 'ON'
		   begin
				select @alt_optvalue = 'LOCAL'
		   end

		else
			begin
				select @alt_optvalue = 'GLOBAL'
			end
	end

if lower(@fulloptname) = 'offline'
	begin
		if @alt_optvalue = 'ON'
		   begin
				select @alt_optname = 'OFFLINE'
		   end

		else
			begin
				select @alt_optname = 'ONLINE'
			end
		select @alt_optvalue = ''
	end

if lower(@fulloptname) = 'read only'
	begin
		if @alt_optvalue = 'ON'
		   begin
				select @alt_optname = 'READ_ONLY'
		   end

		else
			begin
				select @alt_optname = 'READ_WRITE'
			end
		select @alt_optvalue = ''
	end

if lower(@fulloptname) = 'dbo use only'
	begin
		if @alt_optvalue = 'ON'
			begin
				if databaseproperty(@dbname, 'IsSingleUser') = 1
					begin
						raiserror(5066,-1,-1);
						return (1)
					end
				select @alt_optname = 'RESTRICTED_USER'
			end

		else
			begin
				if databaseproperty(@dbname, 'IsDBOOnly') = 0
					begin
						return (0)
					end
				select @alt_optname = 'MULTI_USER'
			end

		select @alt_optvalue = ''
	end

if lower(@fulloptname) = 'single user'
	begin
		if @alt_optvalue = 'ON'
		   begin
				if databaseproperty(@dbname, 'ISDBOOnly') = 1
					begin
						raiserror(5066,-1,-1);
						return (1)
					end
				select @alt_optname = 'SINGLE_USER'
		   end

		else
			begin
				if databaseproperty(@dbname, 'IsSingleUser') = 0
					begin
						return (0)
					end
				select @alt_optname = 'MULTI_USER'
			end
		select @alt_optvalue = ''
	end

if lower(@fulloptname) = 'select into/bulkcopy'
	begin
		select @alt_optname = 'RECOVERY'

		if @alt_optvalue = 'ON'
		   begin
				if databaseproperty(@dbname, 'IsTrunclog') = 1
					begin
						select @alt_optvalue = 'RECMODEL_70BACKCOMP'
					end
				else
					begin
						select @alt_optvalue = 'BULK_LOGGED'
					end
		   end

		else
			begin
				if databaseproperty(@dbname, 'IsTrunclog') = 1
					begin
						select @alt_optvalue = 'SIMPLE'
					end
				else
					begin
						select @alt_optvalue = 'FULL'
					end
			end
	end

if lower(@fulloptname) = 'trunc. log on chkpt.'
	begin
		select @alt_optname = 'RECOVERY'

		if @alt_optvalue = 'ON'
			begin
				if databaseproperty(@dbname, 'IsBulkCopy') = 1
					begin
						select @alt_optvalue = 'RECMODEL_70BACKCOMP'
					end
				else
					begin
						select @alt_optvalue = 'SIMPLE'
					end
			end

		else
			begin
				if databaseproperty(@dbname, 'IsBulkCopy') = 1
					begin
						select @alt_optvalue = 'BULK_LOGGED'
					end
				else
					begin
						select @alt_optvalue = 'FULL'
					end
			end
	end

/* construct the ALTER DATABASE command string */
select @exec_stmt = 'ALTER DATABASE ' + quotename(@dbname) +' SET ' + @alt_optname + ' ' + @alt_optvalue + ' WITH NO_WAIT'
exec (@exec_stmt)

if @@error <> 0
	begin
		raiserror(15627,-1,-1)
		return (1)
	end
else
	begin
		return (0)
	end

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

checkpoint
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 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
revoke execute on sp_MS_marksystemobject to public
go

--------------------------------------------------------------------------------
--	Turn CDOC for cross database ownership chaining on
--	Name has to match exactly
--------------------------------------------------------------------------------
go
exec sp_dboption 'msdb', 'DB Chaining', 'on'
go

if object_id('sp_linkedservers_rowset','P') IS NOT NULL
	drop procedure sp_linkedservers_rowset
go

create proc sp_linkedservers_rowset
	(
	@srvname 	sysname
	)
as
	IF is_srvrolemember('sysadmin') = 1
	begin
		select
			SVR_NAME 		= srvname,
			SVR_PRODUCT		= srvproduct,
			SVR_PROVIDERNAME	= providername,
			SVR_DATASOURCE		= datasource,
			SVR_PROVIDERSTRING = providerstring,
			SVR_LOCATION		= location,
			SVR_CATALOG		= catalog
		from master.dbo.sysservers
		where srvname = @srvname and (srvstatus & 128) = 128
		order by 1
	end
	ELSE
	begin
		select
			SVR_NAME 		= srvname,
			SVR_PRODUCT		= srvproduct,
			SVR_PROVIDERNAME	= providername,
			SVR_DATASOURCE		= datasource,
			SVR_PROVIDERSTRING = NULL,
			SVR_LOCATION		= location,
			SVR_CATALOG		= catalog
		from master.dbo.sysservers
		where srvname = @srvname and (srvstatus & 128) = 128
		order by 1
	end
go


create proc sp_linkedservers_rowset;2
as
	IF is_srvrolemember('sysadmin') = 1
	begin
		select
			SVR_NAME 		= srvname,
			SVR_PRODUCT		= srvproduct,
			SVR_PROVIDERNAME	= providername,
			SVR_DATASOURCE		= datasource,
			SVR_PROVIDERSTRING = providerstring,
			SVR_LOCATION		= location,
			SVR_CATALOG		= catalog
		from master.dbo.sysservers
		where (srvstatus & 128) = 128
		order by 1
	end
	ELSE
	begin
		select
			SVR_NAME 		= srvname,
			SVR_PRODUCT		= srvproduct,
			SVR_PROVIDERNAME	= providername,
			SVR_DATASOURCE		= datasource,
			SVR_PROVIDERSTRING = NULL,
			SVR_LOCATION		= location,
			SVR_CATALOG		= catalog
		from master.dbo.sysservers
		where (srvstatus & 128) = 128
		order by 1
	end
go

grant execute on sp_linkedservers_rowset to public
go

if object_id('sp_linkedservers', 'P') is not null
	drop proc sp_linkedservers
go
raiserror(15339,-1,-1,'sp_linkedservers')
go
create proc sp_linkedservers as
	IF is_srvrolemember('sysadmin') = 1
	begin
		select
			SRV_NAME = srvname,
			SRV_PROVIDERNAME = providername,
			SRV_PRODUCT = srvproduct,
			SRV_DATASOURCE = datasource,
			SRV_PROVIDERSTRING = providerstring,
			SRV_LOCATION = location,
			SRV_CAT = catalog
		from master.dbo.sysservers
		order by 1
	end
	ELSE
	begin
		select
			SRV_NAME = srvname,
			SRV_PROVIDERNAME = providername,
			SRV_PRODUCT = srvproduct,
			SRV_DATASOURCE = datasource,
			SRV_PROVIDERSTRING = NULL,
			SRV_LOCATION = location,
			SRV_CAT = catalog
		from master.dbo.sysservers
		order by 1
	end
go

grant execute on sp_linkedservers to public
go

DENY SELECT (providerstring) ON sysservers TO PUBLIC
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
--------------------------------------------------------------------------------
--	ODBC/OLEDB Catalog Procs (INSTCAT.SQL)
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--	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
