0

I would like to change the Azure SQL Managed Instance admin (this is not for the Active Directory Admin). I do not see any way to do this directly. I am guessing that if I create another sysadmin user, and then delete the current Managed Instance admin user which is also a sysadmin. This might force the backend to fall back and look for any sysadmin available to fill the vacated Managed Instance admin. I have not tried this because the current Azure SQL Managed Instance is in use. I am afraid the Azure SQL Managed Instance might blow up if it loses its Managed Instance admin.

What will occur if one deletes the Managed Instance admin user?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
ppham
  • 17
  • 7

1 Answers1

1

Connect to the instance name from Azure Data Studio and create a new admin user:

  1. Go to the master db and run the create login script

    CREATE LOGIN newusername WITH PASSWORD = 'password123'

  2. Go to your database and run

     use databasename
 go

 Create user [newusername] from login [newusername]
 go
 exec sp_addrolemember 'db_owner', 'newusername';

  3. Remove your old user admin

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Lenroy Yeung
  • 291
  • 3
  • 8
  • I have done steps 1 and 2 already. I have not done step 3. Have you done step 3 and saw no issues with your sql mi afterwards? – ppham Oct 08 '21 at 17:05
  • @ppham I've done the step 3 and no issue at all. One thing I forgot to mention is you need to apply step 2 all your databases on that instance. Please consider upvoting and accepting the answer if you found it helpful. – Lenroy Yeung Oct 09 '21 at 00:31