Wednesday, 20 February 2013

Script To fix all Orphaned users


USE [master]
GO
CREATE PROCEDURE [dbo].[SP_AutoFixOrphanUsers]

AS
SET NOCOUNT ON
---Declare Variables

DECLARE @iDBSUCCEED           INT,
            @iDBFAIL          INT,
            @iAutoID        INT,
            @iMaxAutoID     INT,
            @iRetryAttempts TINYINT,
        @cUserName      NVARCHAR(128) --Variable to hold current UserName to process
     
DECLARE @OrphanUsers TABLE
        (AutoID     INT IDENTITY(1,1),
         UserName    NVARCHAR(128))

-- Initialize Variables
--
SET   @iDBSUCCEED = 0
SET   @iDBFAIL    = -100
SET @iAutoID    = 1
SET @iRetryAttempts = 0

BEGIN TRY

    -- Add Orphan Users into the Temp Table
 -----
    INSERT INTO @OrphanUsers(UserName)
        SELECT  name
        FROM    sysusers
        WHERE   (issqluser = 1)
            AND (sid is not null and sid <> 0*0)
            AND (suser_sname(sid) is null)
        ORDER BY name

    -- Get the Max AutoID
  -----
    SELECT  @iMaxAutoID = MAX(AutoID)
    FROM    @OrphanUsers

    -- Loop through orphan users and fix those
    ---------
    WHILE @iAutoID <= @iMaxAutoID
    BEGIN

        -- Get the UserName
        -----------
        SELECT  @cUserName = UserName
        FROM    @OrphanUsers
        WHERE   AutoID = @iAutoID

            -- Check whether if that login exists here
            -- If it exists then proceed fixing the user
            ------
            IF (SELECT  COUNT(*)
                  FROM    master..syslogins
                  WHERE name = @cUserName) = 1
            BEGIN
                  -- Re-sync the currently selected orphan user
                  -----------
                  EXEC sp_change_users_login 'auto_fix', @cUserName  
            END

        -- Get Next Orphan User
        ----
        SELECT @iAutoID = @iAutoID + 1
    END
 
    -- Return Success
  ---------
    RETURN @iDBSUCCEED

END TRY

-- Error Handling
BEGIN CATCH

    -- Return Failure
      ---------
      RETURN @iDBFAIL

END CATCH


EXEC SP_AutoFixOrphanUsers

No comments:

Post a Comment