none
Backup and Restore database question

    Question

  • I decided to backup database1 from server1 and restore the bak file to database2 from server2(initially there is database1 at server1 and database2 at server 2 with sufficient permissions)

    When I tried restoring at server2, I found out that I had to reassign the database users permissions(why is that so?) as my application is using users to connect to remote database.(from server 1 to 2 and vice versa)

    I cant use the import/export wizard as it takes too long for me to export data over the network connection.

    Does the database after restore retain database permissions?

    I want to just restore the data and not overwrite the database permissions

    Tuesday, November 26, 2013 12:10 PM

Answers

  • A database has users which maps to a login

    The user details are stored in the database and the login is stored in the master database of the instance. They are linked by a unique SID.

    When you restore a database from one server to another server, even if the login exists on the destination server, the SID in the database and the SID in the master database won't match and needs to be re-mapped.

    Tuesday, November 26, 2013 12:36 PM
  • Hello z0802361

    database name does not matter, you could restore with the same name(overwrite,if it already exists) or restore with different name. You still will have to remap the user to the login as long as it copied over from a different server. All replies above, state the reason why you have to do that. The issue here is not the database name, it is login's SID being different on both the servers.

    Default database for login has nothing to do with this. that 's just a default connection to a particular database when you open a query window or connect to a database engine. Even if the default database does not exists, you can connect to the database engine by specfying the database name in the options in SSMS. Again, this has nothing to do with your original question


    Hope it Helps!!



    Wednesday, November 27, 2013 5:17 PM

All replies

  • Yes, it is . If you use SQL Server Authentication to connect to the database, run the below command on the remote server just after restore

    use db

    go

    alter user username with login =loginname

    http://dimantdatabasesolutions.blogspot.co.il/2007/04/sql-or-windows-authentication.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 26, 2013 12:14 PM
  • A database has users which maps to a login

    The user details are stored in the database and the login is stored in the master database of the instance. They are linked by a unique SID.

    When you restore a database from one server to another server, even if the login exists on the destination server, the SID in the database and the SID in the master database won't match and needs to be re-mapped.

    Tuesday, November 26, 2013 12:36 PM
  • Does the database after restore retain database permissions?

    Hello,

    Of course they retain; SQL Server don't change permissions on his own.

    But a SQL24 already wrote, you may have "orphaned users" = SQL logins which don't match by there SID = security ID; see Secure Orphaned User AutoFix how to fix them


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, November 26, 2013 1:06 PM
  • Hi,

    In SQL Server the server logins are mapped to the database users through SID. This mapping information of login and the user resides in the database.

    Lets take the below example:

    Say database1 resides in server1 and  login1 is mapped to user1 in database1. Lets suppose the SID of Login1 is 0xabcd.

    Now let suppose Server2 has database2 with login2 mapped to user2 in database2. But the SID of Login2 will not be same as that of Login1.

    Hence when you backup Database1 and restore it as Database2 on Server2, the restored database basically contains the SID info of Login1 which does not exists on Server2 and hence SQL Server is not able to map Login2 with User1 in the database. Thats why User1 becomes an orphaned user.

    After DB restoration the database permissions are retained by the database users but they are usable only if a corresponding login exists.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Tuesday, November 26, 2013 2:08 PM
  • if the database name is the same, when I backup on server 1, and restore on server 2, I will still have to remap database permissions?

    what is an alternative workaround?

    everytime i have to unmap by 

    1. deleting the user that is mapped to the database

    2. reassign user mapped to the database

    3. assign user permissions

    I have to use sql authentication for user login for database, as one of my applications is using remote connect to my database



    • Edited by z0802361 Tuesday, November 26, 2013 2:49 PM
    Tuesday, November 26, 2013 2:46 PM
  • No, you do not need to delete the users and reassign permissions.  All you need to is remap the login to the user.

    Uri gave the syntax for you above 

    Alter user <<username>> with login =<<loginname>>

    So, After you restore the database on the second server, just run that command and everything else should work as it should. No need to delete  and reassign.

    Users are related to database and logins are related to server. In simple terms,for a successfull connection to a database, you need to go through Server first and then through database. The one that lets you through server is login and the one that lets you through database is User. so, login and User should be linked which is done internally by SID(not by the login name).

    As other said above, user permissions on the database are stored in the database but when you restore database to another server, permissions exists in the database but the link between the login and user is broken because  SID are different. With the command above, you are relinking it. You will have to everytime you move the database to a different server(not needed, if  you are doing it on the same server). 

    that's it is good  pratice to script out this restoration procedure and add this command along with it.


    Hope it Helps!!

    Tuesday, November 26, 2013 3:54 PM
  • it seems that Alter User username with login=loginname is not enough

    I have to grant database permissions also

    Wednesday, November 27, 2013 1:53 AM
  • Hmm.. I am not sure why would need to do it unless you dropped the user.  To my understanding, you are restoring a database from server A to B and in this case, you just need to remap the  user to an existing login on server B.

    Hope it Helps!!

    Wednesday, November 27, 2013 4:30 AM
  • Hi,

    If the same login is present on server2 with the same SID then you won't have to remap again and again.

    To ensure that the same login is present on Server2 also with the same SID, you can use the Login Tranfer task of SSIS to transfer the login along with its SID from Server1 to Server2.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Wednesday, November 27, 2013 4:59 AM
  • I have both Logins on both servers that are mapped to their database?

    Is it recommended that db1 in server 1 and db2 in server 2 have the same database name?

    I tried for different names they also work, if initially there user is mapped to database

    Does the default database of the login user matter?


    Wednesday, November 27, 2013 3:05 PM
  • Hello z0802361

    database name does not matter, you could restore with the same name(overwrite,if it already exists) or restore with different name. You still will have to remap the user to the login as long as it copied over from a different server. All replies above, state the reason why you have to do that. The issue here is not the database name, it is login's SID being different on both the servers.

    Default database for login has nothing to do with this. that 's just a default connection to a particular database when you open a query window or connect to a database engine. Even if the default database does not exists, you can connect to the database engine by specfying the database name in the options in SSMS. Again, this has nothing to do with your original question


    Hope it Helps!!



    Wednesday, November 27, 2013 5:17 PM