none
怎样修改系统存储过程呢?比如说:sp_resolve_logins RRS feed

  • 问题

  • 怎样修改系统存储过程呢?比如说:sp_resolve_logins 。我在SQL2000的企业管理器中打开这一存储过程的属性对话框,但是其内容是只读的。怎么改呢?
    2010年10月9日 10:44

答案

  • http://support.microsoft.com/kb/310882

    提供了解决办法

    use master
    go
    drop procedure sp_resolve_logins
    go
    create procedure sp_resolve_logins
      @dest_db     sysname
      ,@dest_path    nvarchar(255)
      ,@filename    nvarchar(255)
    as
      -- Setup run-time options and 
      -- Declare variables.
      SET NOCOUNT ON
      
      DECLARE  @retcode     int      -- Return value of xp call.
          ,@datafiletype  varchar(255)
          ,@command     nvarchar(255)
          ,@lgnname     sysname
          ,@lgnsid     varbinary(85)
          ,@usrname     sysname
    
      -- Check permissions.
      IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
      BEGIN
       RAISERROR(15247, 16, 1)
       RETURN(1) -- Failure
      END
    
      -- Error if in user transaction.
      IF @@trancount > 0
      BEGIN
        raiserror(15289,-1,-1)
        RETURN (1)
      END
    
      -- Validate the directory the dat file is in.
      -- Remove heading and trailing spaces.
      SELECT @dest_path = RTRIM(LTRIM(@dest_path))
      
      -- If the last char is '\', remove it.
      IF substring(@dest_path, len(@dest_path),1) = '\'
       SELECT @dest_path = substring(@dest_path, 1, len(@dest_path)-1)
    
      -- Do not do validation if it is a UNC path due to security problem.
      -- If the server is started as a service using local system account, we
      -- do not have access to the UNC path.
      IF substring(@dest_path, 1,2) <> '\\'
      BEGIN
        SELECT @command = 'dir "' + @dest_path + '"'
        exec @retcode = master..xp_cmdshell @command, 'no_output'
        IF @@error <> 0
         RETURN (1)
        IF @retcode <> 0 
        BEGIN
         raiserror (14430, 16, -1, @dest_path)       
         RETURN (1)
        END
      END
    
    
      -- CREATE the temp table for the datafile.
      -- This method ensures we are always getting the
      -- real table definition of the syslogins table.
      SELECT  *
      INTO   #sysloginstemp
      FROM   master.dbo.syslogins
      WHERE  sid = 0x00
    
      truncate TABLE #sysloginstemp
    
      -- BULK INSERT the file into the temp table.
      SET   @dest_path = @dest_path + '\' + @filename
      SET   @datafiletype  = '''widenative'''
    
      EXEC('
        BULK INSERT #sysloginstemp 
        FROM ''' + @dest_path + '''
        WITH (
            DATAFILETYPE = ' + @datafiletype + '
            ,KEEPNULLS)
        ')
    
      -- UPDATE the SID in the destination database to the value in the current server's 
      -- syslogins table ensuring that the names match between the source and destination 
      -- syslogins tables. Do this by cursoring through each login and executing
      -- sp_change_users_login for each login that require a SID resynch.
    
      -- DECLARE & OPEN CURSOR over old login names
    	DECLARE loginmapping CURSOR LOCAL FOR SELECT name, sid FROM #sysloginstemp
    	OPEN loginmapping
    
    	FETCH loginmapping INTO @lgnname, @lgnsid
    	WHILE (@@fetch_status >= 0)
    	BEGIN
    
       -- GET NAME OF USER THAT NEEDS TO BE RE-MAPPED FOR THIS LOGIN
    		SELECT @usrname = NULL		-- INIT TO NULL IN CASE OF NO MATCH
    		SELECT @usrname = u.name
    	   FROM dbo.sysusers u
          ,master.dbo.syslogins l
    		 WHERE u.sid = @lgnsid 
         AND l.loginname = @lgnname 
         AND l.sid <> u.sid
    			 
    		-- If we have a user name, do the remapping.
    		IF @usrname IS NOT NULL
    			EXEC ('EXEC ' + @dest_db + '.dbo.sp_change_users_login Update_One, ' + @usrname + ',' + @lgnname)
    
    		-- Get next login-mapping.
    		FETCH loginmapping INTO @lgnname, @lgnsid
    	END
    
      CLOSE loginmapping
      DEALLOCATE loginmapping
    
      -- Return Success/Failure
      IF @@ERROR <> 0
       RETURN (1)
      RETURN (0)
    
    

    有dba的职位吗(北京的),请联系我 stswordman#hotmail.com
    • 已标记为答案 老笨猪 2010年10月11日 3:08
    2010年10月10日 5:01
    版主

全部回复

  • 没改过, 你可以试试alter procedure

    问一句 为什么要修改呢?


    有dba的职位吗(北京的),请联系我 stswordman#hotmail.com
    2010年10月9日 11:12
    版主
  • You can't change system object since sql2k. Why change it in first place. If you like to add your own logic, can make new one based on it.
    2010年10月9日 16:18
  • As documented in Knowledge Base article 310882, “BUG: sp_resolve_logins Stored Procedure Fails If Executed During Log Shipping Role Change,” there is a known issue with the sp_resolve_logins stored procedure that requires a manual fix. This stored procedure exists in the master database. Here’s the problem. The code currently contains

    SELECT * INTO #sysloginstemp FROM syslogins WHERE sid = 0x00

    which is incorrect. This incorrectly uses the syslogins table because it does not qualify it. The new statement should be manually corrected to this:

    SELECT * INTO #sysloginstemp FROM master.dbo.syslogins WHERE sid = 0x00

    2010年10月10日 2:47
  • You should get fix from MS.
    2010年10月10日 3:27
  • http://support.microsoft.com/kb/310882

    提供了解决办法

    use master
    go
    drop procedure sp_resolve_logins
    go
    create procedure sp_resolve_logins
      @dest_db     sysname
      ,@dest_path    nvarchar(255)
      ,@filename    nvarchar(255)
    as
      -- Setup run-time options and 
      -- Declare variables.
      SET NOCOUNT ON
      
      DECLARE  @retcode     int      -- Return value of xp call.
          ,@datafiletype  varchar(255)
          ,@command     nvarchar(255)
          ,@lgnname     sysname
          ,@lgnsid     varbinary(85)
          ,@usrname     sysname
    
      -- Check permissions.
      IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
      BEGIN
       RAISERROR(15247, 16, 1)
       RETURN(1) -- Failure
      END
    
      -- Error if in user transaction.
      IF @@trancount > 0
      BEGIN
        raiserror(15289,-1,-1)
        RETURN (1)
      END
    
      -- Validate the directory the dat file is in.
      -- Remove heading and trailing spaces.
      SELECT @dest_path = RTRIM(LTRIM(@dest_path))
      
      -- If the last char is '\', remove it.
      IF substring(@dest_path, len(@dest_path),1) = '\'
       SELECT @dest_path = substring(@dest_path, 1, len(@dest_path)-1)
    
      -- Do not do validation if it is a UNC path due to security problem.
      -- If the server is started as a service using local system account, we
      -- do not have access to the UNC path.
      IF substring(@dest_path, 1,2) <> '\\'
      BEGIN
        SELECT @command = 'dir "' + @dest_path + '"'
        exec @retcode = master..xp_cmdshell @command, 'no_output'
        IF @@error <> 0
         RETURN (1)
        IF @retcode <> 0 
        BEGIN
         raiserror (14430, 16, -1, @dest_path)       
         RETURN (1)
        END
      END
    
    
      -- CREATE the temp table for the datafile.
      -- This method ensures we are always getting the
      -- real table definition of the syslogins table.
      SELECT  *
      INTO   #sysloginstemp
      FROM   master.dbo.syslogins
      WHERE  sid = 0x00
    
      truncate TABLE #sysloginstemp
    
      -- BULK INSERT the file into the temp table.
      SET   @dest_path = @dest_path + '\' + @filename
      SET   @datafiletype  = '''widenative'''
    
      EXEC('
        BULK INSERT #sysloginstemp 
        FROM ''' + @dest_path + '''
        WITH (
            DATAFILETYPE = ' + @datafiletype + '
            ,KEEPNULLS)
        ')
    
      -- UPDATE the SID in the destination database to the value in the current server's 
      -- syslogins table ensuring that the names match between the source and destination 
      -- syslogins tables. Do this by cursoring through each login and executing
      -- sp_change_users_login for each login that require a SID resynch.
    
      -- DECLARE & OPEN CURSOR over old login names
    	DECLARE loginmapping CURSOR LOCAL FOR SELECT name, sid FROM #sysloginstemp
    	OPEN loginmapping
    
    	FETCH loginmapping INTO @lgnname, @lgnsid
    	WHILE (@@fetch_status >= 0)
    	BEGIN
    
       -- GET NAME OF USER THAT NEEDS TO BE RE-MAPPED FOR THIS LOGIN
    		SELECT @usrname = NULL		-- INIT TO NULL IN CASE OF NO MATCH
    		SELECT @usrname = u.name
    	   FROM dbo.sysusers u
          ,master.dbo.syslogins l
    		 WHERE u.sid = @lgnsid 
         AND l.loginname = @lgnname 
         AND l.sid <> u.sid
    			 
    		-- If we have a user name, do the remapping.
    		IF @usrname IS NOT NULL
    			EXEC ('EXEC ' + @dest_db + '.dbo.sp_change_users_login Update_One, ' + @usrname + ',' + @lgnname)
    
    		-- Get next login-mapping.
    		FETCH loginmapping INTO @lgnname, @lgnsid
    	END
    
      CLOSE loginmapping
      DEALLOCATE loginmapping
    
      -- Return Success/Failure
      IF @@ERROR <> 0
       RETURN (1)
      RETURN (0)
    
    

    有dba的职位吗(北京的),请联系我 stswordman#hotmail.com
    • 已标记为答案 老笨猪 2010年10月11日 3:08
    2010年10月10日 5:01
    版主