fix orphaned users after restore production DB to test platform

HI all,

this question is an extension to http://www.experts-exchange.com/questions/28834639/restore-production-DB-with-replication-setup-to-a-test-platform.html#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.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marrowyungSenior Technical architecture (Data)Author Commented:
and also, how to remove the orphaned users if it can't find it and the sp_change_users_login can't fix it .
0
sachiekCommented:
Simple fix would be like below.

EXEC sp_change_users_login 'Auto_Fix' , 'TheUserName';

Open in new window

0
sachiekCommented:
More steps in details you can find in this site.

http://dev-notes.com/code.php?q=70
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
sachiekCommented:
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

0
sachiekCommented:
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

0
sachiekCommented:
USE [master]
GO

CREATE PROCEDURE [sp_AutoFixAllUsers]
AS
BEGIN

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

    SELECT --dp.[name], dp.[sid] AS [DatabaseSID], sp.[sid] AS [ServerSID],
        @AutoFixCommand = @AutoFixCommand + ' '
         + '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
END
GO

Open in new window


then used the sys.sp_MS_marksystemobject stored procedure to make my stored procedure available in all user databases (allowing it to operate on local objects)

EXEC sys.sp_MS_marksystemobject 'sp_AutoFixAllUsers'

Open in new window

You can then run it as follows:

EXEC [MyDB].[dbo].[sp_AutoFixAllUsers]

Open in new window

Or for every database using sp_msforeachdb:

EXEC sp_msforeachdb '[?].[dbo].[sp_AutoFixAllUsers]'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
wait, there are 3 x scripts there, right? or should one you use usally ?
0
sachiekCommented:
You can use either of the one. All three will work.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
second one doesn't work very well as it output a user already fixed,.
0
sachiekCommented:
What version are you using now?
0
marrowyungSenior Technical architecture (Data)Author Commented:
SQL server 2008 SP4 and SP3
0
sachiekCommented:
I am lost. So where are you struck now?
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
sachiekCommented:
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

0
marrowyungSenior Technical architecture (Data)Author Commented:
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

0
marrowyungSenior Technical architecture (Data)Author Commented:
this one man!

 SET @cmd = 'select  ''sp_change_users_login "update_one", "' + @username + '", "' + @username + '" '' '
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 !
0
marrowyungSenior Technical architecture (Data)Author Commented:
see this one:

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

stop everything from showing up !
0
marrowyungSenior Technical architecture (Data)Author Commented:
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?
0
sachiekCommented:
Right..
0
marrowyungSenior Technical architecture (Data)Author Commented:
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]
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks for it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.