I have removed the function Has_access andįor simplicity. WHERE p.type 'M' - exclude component logins SELECT p.name,įROM master_ p - You need to replace database name here, I have used master_copyĪND r.depsubid = 0 - SRC_LOGIN_CREDENTIAL If you take only the view definition, remove unwanted columns and replace master with master_copy, here is what should give you information which you need. Which means that whenever you query, sys.server_principals, internally its going to go to current master database (hint, hint! I didn't tell the trick so far) If you explore view definition, it's easy to find below Sys.Server_principals is the catalog view which has login information.

(I have tested for SQL 2012, SQL 2008 R2 and it should work with SQL 2005 as well) Is there any way to get this info from master backup without using it to rebuild master of a SQL server Instance? I restored the master on another server withĪ different name but (as expected) couldn't get the details of sys.server_principals for getting the required info. I do have backup of master database on tape (Backup solution used : Litespeed to TSM). The situation we have is that the old server can not be bought online and its data disks etc. : For ‘French’ default language, the first day of the week is ‘Monday’ whereas for logins using ‘us_english’, the first day of week is ‘Sunday’Ī lot of application connect to this server and it would be the last option to contact each application owner for default_language details. As a result, many users started experiencing date related issuesįor e.g. So all logins were created with default language as 'us_english'.

Apparently, this procedure doesn't take care of Default_language of logins. Using sp_help_revlogin ) for transferring logins. I also transferred logins,jobs,linked servers etc. I migrated more than 200 databases from one SQL server to a new SQL server.

Backup sql master