How to Create an Administrator user & grant access to SQL locally using Windows Authentication

(This applies to clients who manage their own Cloud or Dedicated server)

First, if you haven’t created the new local user yet, add the user via "Local Users and Groups" and then add that user to the ‘Administrators‘ group. Once a user is in the Administrator group they can automatically remote desktop into the server.

While still logged in as the main administrator user, proceed to the next steps.

To allow this new user to log into SQL Management Studio locally on the server:

Launch SQL Management Studio.

Expand the ‘Security‘ folder,  right-click on the ‘Logins‘ folder and select "New Login".  Click the ‘Search‘ button and in the ‘Enter the object name to select’ box, type in the username you created in the first step above.  Click ‘Check Names’ and assuming it has found the user, click the OK button.

Next, from the left column, select the ‘Server Roles’ option and grant this user "sysadmin" privileges.   Click OK.

This new user should now be able to remote desktop into the server and perform all functions within SQL Management Studio.