Data Migration Assistant SQL Server to Azure VIEW Server State

0
votes

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

1
sql-serverazuremigration
Does this answer your question? sql view server state permission was denied – Mikael
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

1 Answers

0
votes

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

Please ref: https://docs.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.