存储过程sp_addsrvrolemember原版重建SQL Server 2000错误2812

解决错误 2812: 未能找到存储过程sp_addsrvrolemember,通常在修改用户服务器权限时出现。故障原因可能是由于黑客木马侵入,将sa密码更改,并删除了此存储过程,由此阻碍对用户权限的修改。解决方案就是重建这个存储过程,操作过程如下:

开始菜单中,找打并打开查询分析器,或在运行中输入isqlw直接打开,输入以下代码运行:

create procedure sp_addsrvrolemember
    @loginame sysname,   -- login name
    @rolename sysname = NULL -- server role name
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
 set nocount on
 declare @ret        int,    -- return value of sp call
            @rolebit    smallint,
            @ismem      int,
            @sid        varbinary(85)

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

    -- VALIDATE SERVER ROLE NAME, CHECKING PERMISSIONS --
    select @ismem = is_srvrolemember(@rolename)
    if @ismem is null
    begin
  dbcc auditevent (108, 1, 0, @loginame, NULL, @rolename, NULL)
        raiserror(15402, -1, -1, @rolename)
        return (1)
    end
    if @ismem = 0
 begin
  dbcc auditevent (108, 1, 0, @loginame, NULL, @rolename, NULL)
  raiserror(15247,-1,-1)
  return (1)
 end

 -- AUDIT A SUCCESSFUL SECURITY CHECK --
 dbcc auditevent (108, 1, 1, @loginame, NULL, @rolename, NULL)

    -- OBTAIN THE BIT FOR THIS ROLE --
    select @rolebit = CASE @rolename
            WHEN 'sysadmin'         THEN 16
            WHEN 'securityadmin'    THEN 32
            WHEN 'serveradmin'      THEN 64
            WHEN 'setupadmin'       THEN 128
            WHEN 'processadmin'     THEN 256
            WHEN 'diskadmin'        THEN 512
            WHEN 'dbcreator'        THEN 1024
   WHEN 'bulkadmin'  THEN 4096
            ELSE NULL END

 select @sid = sid from master.dbo.syslogins where loginname = @loginame
    -- ADD ROW FOR NT LOGIN IF NEEDED --
    if @sid is null
    begin
        execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame
        if (@ret <> 0)
    begin
     raiserror(15007,-1,-1,@loginame)
     return (1)
    end
    end
    -- CANNOT CHANGE SA ROLES --
 else if @sid = 0x1 -- 'sa'
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

    -- UPDATE ROLE MEMBERSHIP --
    update master.dbo.sysxlogins set xstatus = xstatus | @rolebit, xdate2 = getdate()
    where name = @loginame and srvid IS NULL

 -- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
 exec('use master grant all to null')

 raiserror(15488,-1,-1,@loginame,@rolename)

    -- FINALIZATION: RETURN SUCCESS/FAILURE
 return (@@error) -- sp_addsrvrolemember
点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注