Share via

SQL Server 2022 : Issue Restoring Database from 2014 Fails Without 'sysadmin' Role

Pagare, Ankit 0 Reputation points
2025-06-12T06:28:17.4766667+00:00

We are attempting to restore a SQL Server 2014 database to SQL Server 2022. The process includes updating the database compatibility level from 120 (SQL 2014) to 160 (SQL 2022) after restoration.

The issue we are encountering is:

  • The restore and compatibility level change work only when the user account has the sysadmin server role.
  • If the same account is used without sysadmin privileges, the restore fails or specific post-restore operations (e.g., compatibility level change or access to the database) do not work as expected.

What is the minimum required permission set (server role or specific database-level permissions) to perform the following:

  • Restore a database
  • Change compatibility level using ALTER DATABASE
  • Is there any official Microsoft documentation or KB article that outlines permission requirements for cross-version restore and compatibility level modification?
  • Are there known changes in SQL Server 2022 security/permissions that might cause this behavior compared to previous versions? C# code used: 
      SQLString = @"USE master; ALTER DATABASE " + DBName + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE;restore database " + DBName + " from disk = @FilePath with replace;ALTER DATABASE " + DBName + " SET MULTI_USER;";
  				                
   SQLString += " ALTER DATABASE " + DBName + " SET COMPATIBILITY_LEVEL=160;";
    connection string used: "User ID=root;Password=root;Persist Security Info=True;Initial Catalog=master;Data Source=(local)\SQLEXPRESS"
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
567 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 47,416 Reputation points
    2025-06-12T11:08:08.8033333+00:00

    All T-SQL commands are well documented including the required permissions, for example the RESTORE command =>

    https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver17#security

    0 comments
  2. Erland Sommarskog 121.3K Reputation points MVP Volunteer Moderator
    2025-06-12T17:32:40.31+00:00

    To restore a database over an existing one (which you are doing), you need to be member of the sysadmin or dbcreator fixed roles, or you need to be the owner of the database.

    To change the compatibility level of the database, you need to be member of the database role db_owner. Note that when you restore a database over an existing one, the owner of the database is retained. So if you only are in the dbcreator role, you may not be able to change the compatibility level after the restore.

    Best practice is to explicitly set the owner after the restore, since else there may be a mismatch of what is recorded in the database and what is recorded in sys.databases.

    All this does not require sysadmin, but the permissions needed are so hefty, that in practice it's better to run as sysadmin.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.