Run SSMS as a non admin

Phillip Johnson 0 Reputation points
2023-05-09T20:41:42.51+00:00

I have a MSSQL server that I am running and have an instance that I need to allow a non admin to be able to administer. This user is using SSMS from his PC to manage the SQL Databases. He is able to do anything he needs to do except it has the question mark and is not allowed to right click on it and restart the services. I have made sure WMI is enabled and allowed remote enable for this user to this. I have enabled DCOM component to allow this user to access it remotely. I have gave permissions to the user to the MSSQL service and the SQLAgent using subinacl. I have ensured WMI and DCOM are enabled for in on the firewall. The user can use powershell and get into the sql server using enter-pssession then he can stop, start, or restart the service using cmdlets. When the user goes into SSMS however it shows a question mark on the server and agent. I can put that user in the Local Administrators group and it goes to the green arrow so I know it is a permissions issue but am unsure of what else is left to do to allow SSMS to be able to restart these services from a remote pc. Any help on this would be appreciated.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,121 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. AniyaTang-MSFT 12,341 Reputation points Microsoft Vendor
    2023-05-10T02:36:38.93+00:00

    Hi @Phillip Johnson

    I found this link for you about Setting up Management Studio for remote SQL Server: https://www.sqlservercentral.com/articles/setting-up-management-studio-for-remote-sql-server.

    You can check if there are any missing steps.

    Best regards,

    Aniya


  2. Olaf Helper 42,576 Reputation points
    2023-05-10T05:12:07.8933333+00:00

    and restart the services

    SQL Server is a Windows Service and restarting a service requeries admin on Windows level, not on SQL Server.

    0 comments No comments