Team Blog | it can be done with little trick. This posting is provided "AS IS" with no warranties, and confers no rights. Once you get in via DAC, you can run above query and get the data from the restored copy of master database.īalmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker In Management studio, you can use Admin: before server name or via SQLCMD you can use -A parameter. The views referred in the query as not accessible with normal connection!! To access them, you need to use DAC connection. If you attempt to run this, you are going to get error because
(SQL 2012 view definition has more columns) 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.
0 kommentar(er)
