怎样修改系统存储过程呢？比如说：sp_resolve_logins

Question

怎样修改系统存储过程呢？比如说：sp_resolve_logins 。我在SQL2000的企业管理器中打开这一存储过程的属性对话框，但是其内容是只读的。怎么改呢？

Answer 1

没改过, 你可以试试alter procedure

问一句 为什么要修改呢?

---

有dba的职位吗(北京的)，请联系我 stswordman#hotmail.com

Answer 2

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.

Answer 3

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

Answer 4

You should get fix from MS.

Answer 5

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