Unanswered: Restoring Master to get SQL login back
Hi Everyone, I'm hoping someone can help me.
We've had a SQL login deleted (special application one) and I understand the best way I can get it back, is to restore the Master database. Is this true ? All the manuals are working on the fact that Master has become corrupt & that it should be rebuilt before restoring. Ours is not corrupt, so am I ok to skip rebuilding it first ?
I wouldn't do that in production environment, because you lose definition of all logins, linked servers, dbs etc. created after the backup you pick to restore. What about restoring master db on test server and using DTS Transfer Logins Task or script out the login and run the script on your server? mojza
If you know the particulars of the login (name and password), it is much easier to re-add the login, rather than restore all of master. Assuming the user has been dropped as well (which Enterprise Manager cheerfully does for you), you would actually not get any benefit from restoring master, as all of the user's permissions in the application database would still be lost. Restoring the user's permissions is what is going to bog you down much more.
If the user was not deleted, then you can run sp_change_users_login to restore the link between the database user and the (new) login.