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

Text File | 2002-09-17 | 7.8 KB | 305 lines

--------------------------------------------------------------------------------
-- VERIFY Server is started in single-user-mode (catalog-updates enabled), and
--    start marking of system-objects.
--------------------------------------------------------------------------------
use master
go

dump tran master with no_log
go

exec dbo.sp_configure 'allow updates',1
go
reconfigure with override
go

set ANSI_NULLS off

exec sp_MS_upd_sysobj_category 1
go


if exists (select * from sysobjects
        where name = 'xp_execresultset')
    revoke execute on dbo.xp_execresultset to public

if exists (select * from sysobjects
        where name = 'xp_displayparamstmt')
    revoke execute on dbo.xp_displayparamstmt to public

if exists (select * from sysobjects
        where name = 'xp_printstatements')
    revoke execute on dbo.xp_printstatements to public


-- Revoke privileges on mswebtasks and stored procedures
USE msdb
go
REVOKE INSERT ON mswebtasks FROM PUBLIC
go
REVOKE DELETE ON mswebtasks FROM PUBLIC
go
REVOKE UPDATE ON mswebtasks FROM PUBLIC
go
REVOKE EXECUTE ON sp_insmswebtask FROM PUBLIC
go
REVOKE EXECUTE ON sp_updmswebtask FROM PUBLIC
go

-- Revoke privileges on stored procedures
USE master
go
REVOKE EXECUTE ON sp_makewebtask FROM PUBLIC

-- Drop the stored procedures if they exist, since they had changed an need to be recreated
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_dropwebtask') AND type = 'P')
   DROP PROCEDURE dbo.sp_dropwebtask
go
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_runwebtask') AND type = 'P')
   DROP PROCEDURE dbo.sp_runwebtask
go
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_cleanupwebtask') AND type = 'P')
   DROP PROCEDURE dbo.sp_cleanupwebtask
go
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_convertwebtasks') AND type = 'P')
   DROP PROCEDURE dbo.sp_convertwebtasks
go

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_readwebtask') AND type = 'P')
   DROP PROCEDURE dbo.sp_readwebtask
go

--====================================================================================
-- sp_dropwebtask: Drops a previously created Web Page Task

CREATE PROCEDURE sp_dropwebtask
        @procname nvarchar(128) = NULL,
        @outputfile    nvarchar(255) = NULL
AS
BEGIN
    DECLARE    @retval int
    SET @retval = 0

    DECLARE    @username nvarchar(256)
    SET @username = (select SUSER_SNAME())


    -- At least one of the parameters have to be NOT NULL
    IF ( (@procname is NULL) AND (@outputfile is NULL) )
    BEGIN
        RAISERROR(16801,11,1)
        RETURN(1)
    END

    EXEC @retval = master..xp_dropwebtask @username, @procname, @outputfile

    IF (@retval <> 0)
    BEGIN
        SET @procname = 'sp_dropwebtask'
        RAISERROR(@retval,11,1, @procname)
    END

    RETURN @retval
END
go

--====================================================================================
-- sp_runwebtask: Runs a previously created Web Page Task and creates the
--            web page

CREATE PROCEDURE sp_runwebtask
        @procname nvarchar(128) = NULL,
        @outputfile    nvarchar(255) = NULL
AS
BEGIN

   DECLARE    @retval int
    SET @retval = 0

   DECLARE    @username nvarchar(256)
   SET @username = (select SUSER_SNAME())

    -- At least one of the parameters have to be NOT NULL
    IF ( (@procname is NULL) AND (@outputfile is NULL) )
    BEGIN
        RAISERROR(16803,11,1)
        RETURN(1)
    END

   -- We added one extra argument to the xp - username
   -- In it we pass the name of the current user, who called the sp_runwebtask
   -- Please see more comments in the xp_runwebtask()

    EXEC @retval = master..xp_runwebtask @username, @procname, @outputfile

    IF (@retval <> 0)
    BEGIN
        SET @procname = 'sp_runwebtask'
        RAISERROR(@retval,11,1, @procname)
    END

    RETURN @retval
END
go

--====================================================================================
-- sp_cleanupwebtask: Internal stored procedure called by Enterprise Manager
--            to clean up web
--            task entries after their system task entry has been
--            deleted. This procedure will return success
--            if there is no web task entry associated with the given
--            task id.

CREATE PROCEDURE sp_cleanupwebtask
        @taskid  int = 0
AS
BEGIN
    DECLARE    @procname nvarchar(128)
    DECLARE    @retval int
    SET @retval = 0

    DECLARE    @username nvarchar(256)
    SET @username = (select SUSER_SNAME())

    IF (@taskid = 0)
    RETURN(1)
    SELECT @procname = (SELECT procname FROM msdb..mswebtasks WHERE taskid = @taskid)

    --  Return if there is no such task
    IF (@procname is NULL)
    RETURN(1)

    EXEC @retval = master..xp_cleanupwebtask @username, @procname
    RETURN @retval
END
go

--====================================================================================
-- sp_convertwebtasks: Converts 6.5 webtasks to 7.0 format
--
CREATE PROCEDURE sp_convertwebtasks
AS
BEGIN
    DECLARE    @retval int
    DECLARE    @procname nvarchar(128)
    DECLARE    @thisproc nvarchar(128)
    DECLARE @wpw_65 varbinary(5)
    DECLARE @TotalConverted    int
    DECLARE    @TotalFailed    int

   DECLARE    @username nvarchar(256)
   SET @username = (select SUSER_SNAME())

    -- Are there any webtasks to convert?
    IF ((SELECT count(*) FROM msdb..mswebtasks) = 0)
        goto DONE

    SET @TotalConverted = 0
    SET @TotalFailed = 0

    -- Initialize variables
    SET    @thisproc = 'sp_convertwebtasks'
    SET    @retval = 0
    SET    @wpw_65 = 0x00

    -- Loop through all tasks and convert to 7.0 format
    DECLARE webtaskCur cursor FOR
        SELECT procname FROM msdb..mswebtasks
        WHERE substring(wparams,1,2) = @wpw_65        -- version 6.5

    OPEN webtaskCur
    FETCH webtaskCur INTO @procname

    WHILE (@@fetch_status = 0)
    BEGIN
        EXEC @retval = master..xp_convertwebtask @username, @procname
        IF (@retval <> 0)
        BEGIN

            RAISERROR('%s: Failed to convert webtask from 6.5 to 7.0 format.  You need to use sp_makewebtask to recreate the task',16,1,@thisproc, @procname) WITH LOG
            SET @TotalFailed = @TotalFailed + 1

        END
        ELSE
        BEGIN

            -- Increment successfully converted task count
            SET @TotalConverted = @TotalConverted + 1

            -- Tag webtasks in sysjobs.  Web Assistant tasks are category 4.
            UPDATE msdb.dbo.sysjobs
            SET category_id = 4
            WHERE name = @procname

        END

        FETCH webtaskCur INTO @procname
    END

    CLOSE webtaskCur
    DEALLOCATE webtaskCur

    RAISERROR('%s: %d web tasks converted successfully.  %d webtasks failed to convert.',0,1,@thisproc, @TotalConverted,@TotalFailed) WITH LOG

-- Done with conversion

DONE:
    RETURN @retval

END
go
--====================================================================================
-- sp_readwebtask: retreive web task parameters
--
CREATE PROCEDURE sp_readwebtask
        @procname nvarchar(128) = NULL
AS
BEGIN
    DECLARE @retval int
    SET @retval = 0

    DECLARE    @username nvarchar(256)
    SET @username = (select SUSER_SNAME())

    -- If the procedure name is NULL, Display a list of web tasks
    IF NOT EXISTS (SELECT * FROM msdb..mswebtasks WHERE procname = @procname)
    BEGIN
        SET @retval = 16815
        RAISERROR(@retval,11,1,@procname)
        RETURN(@retval)
    END

    -- Execute xp_readwebtask given the procedure name
    EXEC @retval = master..xp_readwebtask @procname

    IF (@retval <> 0)
    BEGIN
        SELECT @procname = 'sp_readwebtask'
        RAISERROR(@retval,11,1, @procname)
    END

    RETURN @retval
END
go


--====================================================================================
GRANT EXECUTE ON sp_runwebtask TO PUBLIC
go
GRANT EXECUTE ON sp_readwebtask TO PUBLIC
go


exec sp_MS_upd_sysobj_category 2
go

exec sp_configure 'allow updates',0
go

reconfigure with override
go