May 10, 2023 at 2:19 pm
I have a user that needs full access to a specific instance on our SQL Server (2019). I do not want this user to have local or domain admin prividleges. I have gotten SSMS on his pc and have given him a domain account (localAdmin1) to use that does have local admin prividleges on his local machine. I am having him open SSMS as an administrator and use this (localAdmin1) account to open it. I have given this domain account full permissions to the MSSQL$[instanceName] and SQLAgent$[instanceName] service. I used SC tool and I also looked at it using subinacl. I went into WMI control and then went to the security tab and then to Root\CIMV2 folder and added this account to here and gave it full permissions. I went into dcomdnfg and then opened component services\computers\ then when to the properties of "My Computer" from here I went to the COM Security tab and opened Edit Limits for each and gave the user full access. I have done all this trying to get the Question mark to go away on SSMS. If I add the user to the local administrators group of the sql server the question mark is replaced by the green arrow and I can right click on the server and restart, stop, or start the services. If I take the user out of this group I get the Question mark and cannot restart, stop, or start the service by right clicking on the server.
Note: I have put ssms on the sql server itself. If I grant the user remote login to the sql server and have him logon then he can open ssms on the sql server and the green arrow is there and all works fine. The issue is when he is running ssms on his desktop that it happens. This would lead me to think permissions but why would it work localy logged in as that domain user but not when coming in from the network. I do not believe it is a firewall issue as we can get it to work just fine by putting him in the administrators group but being able to logon locally to this server and run it with no problems makes me think it could be a firewall or some network setting. I can open powershell on the users computer and enter-pssession to the sql server and then can start, stop, or restart the services but cannot do it without first getting onto the sql server
May 11, 2023 at 4:48 am
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply