DB permissions not applied after restore RRS feed

  • Question

  • During migration, On new server  the backup was restored which also already has a existing database from old server and after that  db roles and permissions are not updated as  like from old server db's . What may be the issue here ?


    Saturday, August 25, 2018 6:44 PM

All replies

  • Not sure exactly what you are saying:

    A: Are you saying that you restored a database from, say server A on, say server B and the permissions in the database on B doesn't match those you had on A? That is impossible, since a backup/restore is a binary copy of the database. There is no in between.

    B: Or are you expecting to keep some permissions from the previous database on B, after you restored over that database with the backup from server A? If so, you are out of luck. The database is 100% overwritten when you restore over it. Nothing is kept.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Saturday, August 25, 2018 7:08 PM
  • In addition , have you run ALTER USER name WITH LOGIN =name 

    BTW, what version are you using?

    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

    Sunday, August 26, 2018 5:44 AM
  • Server A--> SQL Server 2014 and  Server B--> SQL Server 2016 .


    Monday, August 27, 2018 2:28 PM
  • Doesn't change anything, that you are restoring to a higher version. Please see my post and tell us which of the two alternatives I mentioned that you are doing.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, August 27, 2018 4:56 PM
  • I agree with Tibor. DB users, Permissions etc will be carried over. Can you elaborate more on what you were expecting and what exactly is missing post restore? perhaps you have orphan users or something that you are mistaking for permissions issue. 

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, August 27, 2018 10:38 PM