3

About a month ago, we migrated a production database and logins to a new instance. The client is currently troubleshooting an issue and wants to confirm that the login permissions in the new instance match exactly what they were in the old instance.

We have long since deleted the old logins as well as the database that was migrated from the old instance. However we have backups of the master database. I though I could restore the master as a regular user database and query the views to peek at the server and database principals as they existed before the migration, but the views appear to be returning current information rather than historical.

So for one, there's a fundamental misunderstanding on my part regarding what is contained in the master database (or at least how the views work). If anyone can shed some light on this, that would be great. But mostly, does anyone know if what I'm trying to do is possible?

Edit: As a test, I restored the same backup of the master database onto a completely different instance, naming the restored database: master_temp. Then I tried the following:

SELECT * FROM master_temp.sys.server_principals

The results that were returned only contained information from the instance the database was restored onto. Not a single user login from the instance the backup was taken from was returned in the result set.

Improve this question
4
  • Are you able to query sys.server_principals in the restored copy of that database?
    – Randolph West
    Oct 31, 2016 at 20:30
  • I am, but unfortunately the result set is returning current data, not data from when the backup was actually taken. This is true even though I specifically reference the restored copy of the database, which I named master_temp. So my SELECT is against: master_temp.sys.server_principals
    – Mike W
    Oct 31, 2016 at 20:50
  • I can confirm that it goes straight to the sysxlgns and syspalnames tables on the instance. I reckon if you connect to the Direct Admin Connection, it might work.
    – Randolph West
    Oct 31, 2016 at 21:16
  • 1
    I was able to do it through the DAC, using a modified version of the sys.server_principals to read the data. As this is unsupported, I'm not going to contribute an answer. My recommendation is to spin up a test instance of SQL Server Developer in a VM, and restore master the right way.
    – Randolph West
    Oct 31, 2016 at 21:50

2 Answers 2

Reset to default
0

I would search the system tables in your restored master database directly, that contain the data you are looking for. You can then access for example the sys.sysxlgns table without having to rely on the sys.server_principals view which actually accesses the master.sys.sysxlgns system base table.

sysxlgns = server_principals

For a list of system base tables read the following MSDN article:

System Base Tables

Microsoft states that ...

The system base tables are used only within the SQL Server Database Engine and are not for general customer use. They are subject to change and compatibility is not guaranteed.

Additionally you would have to connect using DAC:

Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario.

DISCLAIMER: This information is provided solely for educational purposes. Deleting data in the base tables can corrupt your database!

Improve this answer
1

While this is possible to do with the Dedicated Admin Connection and a modified version of sys.server_principals, it is unsupported.

I would rather spend this time to restore master properly on a new instance (on Developer Edition under a VM makes the most sense), and then access it from there.

Improve this answer
1
  • Thanks Randolph, if I could choose two answers I'd choose yours too, especially since I'm going with the method you outlined in the comments of the original question (which I believe is detailed in the answer I chose). Unfortunately, spinning up VM's and getting SQL installed in my organization takes weeks, so although your answer is safer, undocumented but quicker wins the day (this time).
    – Mike W
    Nov 2, 2016 at 17:14

Your Answer

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

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