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.
Best Answer
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 ...
Additionally you would have to connect using DAC:
DISCLAIMER: This information is provided solely for educational purposes. Deleting data in the base tables can corrupt your database!