1

Our admin has backed-up (in Veeam) a windows server with SQL server (v 2016) installed on it.

Then he uninstalled the SQL server and installed a newer version - 2019. Then he tried to restore the backup on another machine, but unsuccessfully. He has submitted a support ticket. [The official manual - Transfer logins and passwords...][1] requires the original SQL's instance to be running, but is there another way to recover (the original) SQL server's logins and roles (even SQL jobs) from mdf's (we have them available) while we are waiting for a support from Veeam?

We have a little time left... Thanks for any help

  [1]: https://learn.microsoft.com/en-us/troubleshoot/sql/security/transfer-logins-passwords-between-instances
0

1 Answer 1

2

Restore the old [master] database files as:-

master_recover.mdf
master_recover.ldf

Attach the files as a new database called [master_recover]:-

CREATE DATABASE [master_recover] ON 
( FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master_recover.mdf' ),
( FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master_recover.ldf' )
 FOR ATTACH
GO

Open a query window using DAC (DAC from SSSMS):-

ADMIN:. (local un-named Instance)

Query the old Login data using three-part-naming to access the recovered base table:-

SELECT * 
FROM [master_recover].[sys].sysxlgns;

Create new Logins based on the recovered data.

Server Role membership can similarly be extracted by querying the recovered base table:-

SELECT ROLES.[name] AS ServerRoleName,
       MEMBERS.[name] AS LoginName
FROM [master_recover].[sys].sysmultiobjrefs AS SRM
    INNER JOIN [master_recover].[sys].sysxlgns AS ROLES
        ON ROLES.id = SRM.indepid
    INNER JOIN [master_recover].[sys].sysxlgns AS MEMBERS
        ON MEMBERS.id = SRM.depid
WHERE SRM.class = 26
      AND SRM.depsubid = 0
      AND SRM.indepsubid = 0
      AND MEMBERS.[sid] NOT IN ( 0x1 )
ORDER BY ROLES.[name],
         MEMBERS.[name];

These base tables and queries can be found by looking at the definition of the system views sys.server_principals and sys.server_role_members.

3
  • perfect, thanks, When I create the logins based on the sysxlgns could I transfer the server-roles membership to them, kind of like this here: Transfer logins and passwords... ? Thank you
    – myro
    Dec 30, 2022 at 7:34
  • 1
    @myro I've edited the query in the answer to return ServerRoleName and LoginName, Dec 30, 2022 at 9:37
  • awesome, thank you very much sir!
    – myro
    Dec 30, 2022 at 11:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.