0

I have a SQL Server 2012 database that I want to migrate to Azure SQL using the Data Migration Assistant v5.2. When I get to the Select Target section, I enter a SQL Authentication account that is in the dbmanager group. I get an error: VIEW SERVER STATE permission was denied on object 'server', database 'master'. The user does not have permission to perform this action. And can go no further. How do I get past this?

Thank you, Dep

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dep
  • 1
  • Does this answer your question? [sql view server state permission was denied](https://stackoverflow.com/questions/44106031/sql-view-server-state-permission-was-denied) – Mikael Jul 30 '20 at 14:53
  • No. There is no VIEW SERVER STATE permission in Azure SQL. I have no problem connecting to the database server in SSMS. But the Data Migration Assistant is where I am getting the error. – Dep Jul 30 '20 at 19:06

1 Answers1

0

To run an assessment(DMA), you have to be a member of the SQL Server sysadmin role.

Please ref: https://learn.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15#prerequisites

There are two workarounds we could found before we migrate the on-premise SQL server to Azure SQL:

Workaround 1:

From the SQL Server Management Studio via statement:

  1. Connect to the on-premise SQL instance
  2. Select New Query
  3. Use the following statement
    • GRANT VIEW SERVER STATE TO "AccountName"

Workaround 2:

Update your SSMS to a new version.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • I must not be explaining my self well. I have no problem on the on-premise SQL Server. That part is not a problem. When the DMA tool connects to the Azure SQL Server to choose the receiving database, is where I get the error. – Dep Jul 31 '20 at 14:29
  • same issue for me – Eric Weintraub Mar 22 '22 at 19:46