SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    User Logins on a MS SQL DB after backup and restore

    Let's say I have a DB with 2 users on it. If I backup that database and move it to another server that has logins with the exact same name, I note that when I restore the database, the user logins still exist under the security of the database, but I have to manually remove them, and then go into the server's security logins section and setup the logins to have rights again to the restored DB.

    Is there a way to minimize this or is it natural that the DB says "These logins might HAVE the same name, but since they exist between 2 different servers, they are not actually the SAME user, and you'll have to do what you're doing in order for the logins to work on that restored location"?

  2. #2
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can create script and just run after restore database on different server

  3. #3
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think you should remove login in database fist and recreate login
    database -> security -> user
    remove login
    sorry for my English.

  4. #4
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Yes, it is natural in an MSSQL sense -- logins are actually identified by SIDs not names. The logins don't exist in the database, they exist on the server so anything granted to them dies with the login you failed to move. A better pattern for MSSQL database security is to use database roles and assign the permissions to them. Said roles live with and travel with the database. So, when you have a restore scenario like you are talking about alls you need to do is create appropriate logins and put them in the right groups.


Bookmarks

Posting Permissions

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