Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Sydney AU
    Posts
    15

    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 ?

    Cheers

  2. #2
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    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

  3. #3
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    As to rebuild, you don't need to do that. Just restore master from backup over existing master. Look up How to restore the master database (Transact-SQL) topic in Books Online. mojza

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •