Start Free Trial

asked on

fix orphaned users after restore production DB to test platform

HI all,

this question is an extension to https://www.experts-exchange.com/questions/28834639/restore-production-DB-with-replication-setup-to-a-test-platform.html?anchorAnswerId=41268750#a41268750

they are highly related.

now we have restored the production DB to TEST platform and now we have a lot of orphaned users to fix using sp_change_users_login, once we ran this:

sp_change_users_login 'report'
go

we have a list of this kind of users to fix and now I have to fix it one by one, any way to automate the whole thing and let the result of the command about plug into another script to execute them one by one using?

use <database>
go

sp_change_users_login 'update_one', '<username>', '<username>'
go

we should ignore any SQL user id in production but not in TEST platform as it will show error, which doesn't matter:

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 208
Terminating this procedure. The Login name '<user name>' is absent or invalid.

ASKER

and also, how to remove the orphaned users if it can't find it and the sp_change_users_login can't fix it .

Simple fix would be like below.

EXEC sp_change_users_login 'Auto_Fix' , 'TheUserName';

Open in new window

More steps in details you can find in this site.

http://dev-notes.com/code.php?q=70

ASKER

tks,

but this is not what I want, what if this one

sp_change_users_login 'report'

show me a list of user and how can we fix it all at one time? any script to use ?

There is two type of script.

DECLARE @Username VARCHAR(100),
        @cmd      VARCHAR(100)

DECLARE userlogin_cursor CURSOR FAST_FORWARD FOR
  SELECT username = name
  FROM   sysusers
  WHERE  issqluser = 1
     AND (sid IS NOT NULL
          AND sid <> 0x01)
     AND Suser_sname(sid) IS NULL
  ORDER  BY name
  FOR READ ONLY

OPEN userlogin_cursor
FETCH NEXT FROM userlogin_cursor INTO @Username

WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @cmd = 'ALTER USER [' + @username + '] WITH LOGIN = [' + @username + ']'
      EXECUTE(@cmd)
      FETCH NEXT FROM userlogin_cursor INTO @Username
  END

CLOSE userlogin_cursor
DEALLOCATE userlogin_cursor

Open in new window

DECLARE @UserCount INT
DECLARE @UserCurr INT
DECLARE @userName VARCHAR(100)
DECLARE @vsql NVARCHAR(4000)
DECLARE @Users TABLE(
id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
userName VARCHAR(100))
INSERT INTO @Users(UserName) 
SELECT [name] FROM 
--
master.[dbo].sysUsers -- SQL 2008 & SQL 2005
--master.dbo.sysxlogins -- SQL 2000


SELECT @UserCount = max([id]) FROM @Users
SET @UserCurr = 1

WHILE (@UserCurr <= @UserCount)
BEGIN
 SELECT @userName=userName FROM @Users WHERE [id] =@UserCurr
 SET @vsql = '[dbo].[sp_change_users_login] ''AUTO_FIX'',''' + @userName + ''''
 -- EXEC(@vsql)
 PRINT @vsql
 SET @UserCurr = @UserCurr + 1
END

Open in new window

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

wait, there are 3 x scripts there, right? or should one you use usally ?

You can use either of the one. All three will work.

ASKER

as the script try to find out these users is :

sp_change_users_login 'report'
go

it seems some user is not going to output from your script
?

"guest" is not from:

sp_change_users_login 'report'
go

does it matter ?

ASKER

one thing, the middle one don't work with SQL 2012 and SQL 2014 ? so your first and third script work with SQL 2012/2014 ?

ASKER

second one doesn't work very well as it output a user already fixed,.

What version are you using now?

ASKER

SQL server 2008 SP4 and SP3

I am lost. So where are you struck now?

ASKER

ok I want to use your script to implement the dynamic SQL to change up this kind of users and from Dynamic SQL I have to change the command to :

sp_change_users_login "update_one", "zzDDLAdmin", "zzDDLAdmin"
go

instead of

sp_change_users_login 'update_one', 'zzDDLAdmin', 'zzDDLAdmin.
go

I found script one is better and then I do this:

DECLARE @Username VARCHAR(100),
        @cmd      VARCHAR(100)

DECLARE userlogin_cursor CURSOR FAST_FORWARD FOR
  SELECT username = name
  FROM   sysusers
  WHERE  issqluser = 1
     AND (sid IS NOT NULL
          AND sid <> 0x01)
     AND Suser_sname(sid) IS NULL
  ORDER  BY name
  FOR READ ONLY

OPEN userlogin_cursor
FETCH NEXT FROM userlogin_cursor INTO @Username

WHILE @@FETCH_STATUS = 0
  BEGIN
 SET @cmd = 'select  ''sp_change_users_login "update_one", "' + @username + '", "' + @username ''
    -- SET @cmd = 'ALTER USER [' + @username + '] WITH LOGIN = [' + @username + ']'
      EXECUTE(@cmd)
      FETCH NEXT FROM userlogin_cursor INTO @Username
  END

CLOSE userlogin_cursor
DEALLOCATE userlogin_cursor

Open in new window

but the error message now is:

Msg 102, Level 15, State 1, Line 24
Incorrect syntax near ''.

anything missed ?

Remove what you have for this line and add this line.
In the last you had "Single quote". Should be double quote

SET @cmd = 'select  ''sp_change_users_login "update_one", "' + @username + '", "' + @username '"

Open in new window

ASKER

seems not

error is :

Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '"
-- SET @cmd = '.
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near '
-- real command : sp_change_users_login '.
Msg 105, Level 15, State 1, Line 26
Unclosed quotation mark after the character string ' go
EXECUTE(@cmd)
FETCH NEXT FROM userlogin_cursor INTO @Username
END

CLOSE userlogin_cursor
DEALLOCATE userlogin_cursor

'.

this way right ?

WHILE @@FETCH_STATUS = 0
  BEGIN
  SET @cmd = 'select  ''sp_change_users_login "update_one", "' + @username + '", "' + @username '"
     -- SET @cmd = 'ALTER USER [' + @username + '] WITH LOGIN = [' + @username + ']'
	 	-- real command : sp_change_users_login 'update_one', 'mscholtes', 'mscholtes' go
      EXECUTE(@cmd)
      FETCH NEXT FROM userlogin_cursor INTO @Username
  END

Open in new window

ASKER

this one man!

SET @cmd = 'select ''sp_change_users_login "update_one", "' + @username + '", "' + @username + '" '' '

ASKER

I am testing the 3rd edition you show me by this part only:

DECLARE @AutoFixCommand NVARCHAR(MAX)
    SET @AutoFixCommand = ''

    SELECT --dp.[name], dp.[sid] AS [DatabaseSID], sp.[sid] AS [ServerSID],
        @AutoFixCommand = @AutoFixCommand + ' '

		+  'select  ''sp_change_users_login "update_one", "' + dp.[name]+ '", "' + dp.[name] + '" '' '
     --     + 'EXEC sp_change_users_login ''Auto_Fix'', ''' + dp.[name] + ''';'-- AS [AutoFixCommand]
    FROM sys.database_principals dp
    INNER JOIN sys.server_principals sp
        ON dp.[name] = sp.[name] COLLATE DATABASE_DEFAULT
    WHERE dp.[type_desc] IN ('SQL_USER', 'WINDOWS_USER', 'WINDOWS_GROUP')
    AND sp.[type_desc] IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
    AND dp.[sid] <> sp.[sid]

    IF (@AutoFixCommand <> '')
    BEGIN
        PRINT 'Fixing users in database: ' + DB_NAME()
        PRINT @AutoFixCommand
       -- EXEC(@AutoFixCommand)
        PRINT ''
    END

Open in new window

but result shows me nothing and I am not sure why PRINT thing there show me no result and I can't debug this !

ASKER

see this one:

-- AND dp.[sid] <> sp.[sid]

stop everything from showing up !

ASKER

it seems that this logic:

  DECLARE @AutoFixCommand NVARCHAR(MAX)
    SET @AutoFixCommand = ''

    SELECT --dp.[name], dp.[sid] AS [DatabaseSID], sp.[sid] AS [ServerSID],
        @AutoFixCommand = @AutoFixCommand + ' '

		+  'select  ''sp_change_users_login "update_one", "' + dp.[name]+ '", "' + dp.[name] + '" '' '
     --     + 'EXEC sp_change_users_login ''Auto_Fix'', ''' + dp.[name] + ''';'-- AS [AutoFixCommand]
    FROM sys.database_principals dp
    INNER JOIN sys.server_principals sp
        ON dp.[name] = sp.[name] COLLATE DATABASE_DEFAULT
    WHERE dp.[type_desc] IN ('SQL_USER', 'WINDOWS_USER', 'WINDOWS_GROUP')
    -- AND sp.[type_desc] IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
    -- AND dp.[sid] <> sp.[sid]

Open in new window

show result not the same as

sp_change_users_login 'report'
go

it return much more than that,.

basically we should verify with result from

sp_change_users_login 'report'
go

right?

Right..

ASKER

so any idea on the third script ? is this condition make it doesn't work as expected:

-- AND sp.[type_desc] IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
-- AND dp.[sid] <> sp.[sid]

ASKER

tks for it.