October 27, 2010 at 10:57 am
SQL Server 2008 R2 RTM build
SQL Server account has following rights as per select * from fn_my_permissions(null, 'server')
serverCONNECT SQL
serverVIEW ANY DATABASE
However, when connecting via SSMS as the above mentioned user - I can shutdown SQL Server,
I have tried deny & revoke shutdown to no effect
this account is by default a member of public server role
Public server role has got connect on endpoints
Any advice?
Thank you
October 28, 2010 at 6:41 am
this is actually a "domain" policy. the users that have SSMS installed on their local PC's should be "not allowed" to control system services.
I am not a AD admin nor a GPO guru so I cannot tell you exactly what to change, but somewhere in the Group Policy Management Console of the Domain Controller is a setting that can Disallow this.
probably more info here;
October 28, 2010 at 7:08 am
Hi Geoff,
Thank you for your advice
But this account is a sql server account, not a windows one.
I assume when I login to SSMS va SQL Server Account - I should only have the rights and priviledges assigned to the user.
And another caveat - the server edition in question is Standard edition
I have tested the same account (using same script) against evaluation edition of SQL Server 2008 R2 which is deployed on the same domain - it is fine there.
I am at a loss, there is no difference but for sql server edition..?..
October 28, 2010 at 7:24 am
you may be right, but i can share this.
domain 1 - user domain1 is an admin.
domain 2 - user domain1 is just a user.
domain 2 - user domain2 is an admin.
all sql servers have an sa account.
if i log in via SSMS as domain1 to a server on domain 2, neither account can stop or start sql.
if i log in via SSMS as sa to a server on domain 2, i cannot stop or start sql.
if i log in via SSMS as sa or domain1 to a server on domain 1, i can do whatever i want.
if i start SSMS impersonating user domain2 and then login as sa to a server in domain 2, i can do what ever i want.
that is why i think it might have something to do with either your local security policy or your domain GPO....
October 28, 2010 at 7:49 am
Make sure that the login is not also a member of sysadmin server role. If a login is part of the sysadmin role, then it’s permissions are not being checked. This can explain how come you issue a deny permission statement that doesn’t seem to have any effect.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 28, 2010 at 7:53 am
Hi Adi,
These are my sysadmin role members
sysadminsaSQL_LOGIN
sysadminNT AUTHORITY\SYSTEM WINDOWS_LOGIN
sysadminNT SERVICE\MSSQLSERVER WINDOWS_GROUP
sysadminNT SERVICE\SQLSERVERAGENT WINDOWS_GROUP
Thank you
October 28, 2010 at 10:52 am
I did some more research on this and I am confident that I am correct. Here is my example.
I am a domain admin at acme.com
I am a domain admin at cisco.com
I have sa accounts on both servers.
I start SSMS with my acme.com login and log into a SQL Server in acme.com as "sa" - I can restart SQL via SSMS.
I start SSMS with my cisco.com login and log into a SQL Server in acme.com as "sa" - I cannot restart SQL via SSMS.
I start SSMS with my cisco.com login and log into a SQL Server in cisco.com as "sa" - I can restart SQL via SSMS.
I start SSMS with my acme.com login and log into a SQL Server in cisco.com as "sa" - I cannot restart SQL via SSMS.
Of course, the domain names have been changed, but I just verified this at my workplace.
October 31, 2010 at 12:06 pm
Hi Geoff,
Thank you for your help, well I have come to the conclusion that this is a "feature" or bug of Microsoft build - having played around and having read SQL 2005 discussion - ssms white circle/green arrow.
Whatever your permissions/group policy is if the sql server account doesn't have the right to shutdown sql server via sql server permissions it should not be able to do it.
October 31, 2010 at 3:03 pm
SSMS is not issuing a SQL command to perform the shutdown but instead, SSMS sending a windows command, so the security is based on the Window Login.
To demonstrate, do the following:
Do not run SSMS.
Open the start menu, select run and enter "services.msc"
From the menu, pick "actions" and then pick "Connect to another computer"
In the pop-up, enter the name of the windows server hosting your SQL Server.
In the services list, locate SQL Server - the name will be something like "SQL Server (MSSQLSERVER)"
Right click and select "STOP"
At this point , you have not connected to SQL Server but have stopped SQL Server.
Here is untested Window Management Interface script to stop a service:
Option Explicit
Dim objWMIService, objItem, objService
Dim colListOfServices, strComputer, strService,strComputer = "."
strService = "SQL Server (MSSQLSERVER)"
Set objWMIService = GetObject("winmgmts: {impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colListOfServices = objWMIService.ExecQuery ("Select * from Win32_Service Where Name =" & strService & " ")
For Each objService in colListOfServices
objService.StopService()
Next
WScript.Echo strService & " service has been stopped"
WScript.Quit
SQL = Scarcely Qualifies as a Language
November 1, 2010 at 10:50 am
have you checked the local administrators group on the server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 4, 2010 at 2:43 am
Geoff A (10/28/2010)
this is actually a "domain" policy. the users that have SSMS installed on their local PC's should be "not allowed" to control system services.I am not a AD admin nor a GPO guru so I cannot tell you exactly what to change, but somewhere in the Group Policy Management Console of the Domain Controller is a setting that can Disallow this.
probably more info here;
This isn't correct. The ability to shutdown and restart a service is a permission at the server level. By default, Domain Admins are members of the local Administrators group on a server. The Administrators and Power Users groups on servers have the ability to shutdown services. While membership of these groups can be enforced by GPO, it's not a GPO setting or an AD setting that's giving out such permissions.
K. Brian Kelley
@kbriankelley
November 4, 2010 at 2:50 am
Perry Whittle (11/1/2010)
have you checked the local administrators group on the server?
Where a SQL Server command does this is if you open up a new query window and execute SHUTDOWN, which is a T-SQL command. In that case, the permissions which are checked are internal to SQL Server. If the shutdown is not being done this way, then take the SQL Server permissions out of the picture. So if someone is stopping and restarting SQL Server using the object explorer in SQL Server Management Studio (SSMS) then that's not using SQL Server internal permissions, but Windows permissions.
Therefore, ignore the fact that a SQL Server based login is being used. The key is what user context is SSMS running under? Unless someone has explicitly started SSMS using RunAs, it will run under the context of the logged on user for that workstation/laptop/etc. where they are running SSMS from.
Likely that user is a member of a group that is either a member of the Administrators or Power Users group on the system where SQL Server is deployed. If that's the case, unless you revoke membership in some way to get the user out of those security groups, they will always have the permission to stop/restart SQL Server. If you have a system administrator, it might be a good idea to get with him or her to figure out exactly how the groups are nested.
K. Brian Kelley
@kbriankelley
November 4, 2010 at 4:19 am
K. Brian Kelley (11/4/2010)
Geoff A (10/28/2010)
this is actually a "domain" policy. the users that have SSMS installed on their local PC's should be "not allowed" to control system services.I am not a AD admin nor a GPO guru so I cannot tell you exactly what to change, but somewhere in the Group Policy Management Console of the Domain Controller is a setting that can Disallow this.
probably more info here;
This isn't correct. The ability to shutdown and restart a service is a permission at the server level. By default, Domain Admins are members of the local Administrators group on a server. The Administrators and Power Users groups on servers have the ability to shutdown services. While membership of these groups can be enforced by GPO, it's not a GPO setting or an AD setting that's giving out such permissions.
it is true. where a domain controller is present, local security policy will be overwritten by the domain security policy. i know that can be changed in some enviroments, but that is not the norm....
November 4, 2010 at 4:51 am
K. Brian Kelley (11/4/2010)
Likely that user is a member of a group that is either a member of the Administrators or Power Users group on the system where SQL Server is deployed.
Which is why I said check the local administrators group, people rarely use power users in my experience. Ignorance leads them to dump users into the administrators group instead!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 4, 2010 at 2:36 pm
Geoff A (11/4/2010)
K. Brian Kelley (11/4/2010)
Geoff A (10/28/2010)
this is actually a "domain" policy. the users that have SSMS installed on their local PC's should be "not allowed" to control system services.I am not a AD admin nor a GPO guru so I cannot tell you exactly what to change, but somewhere in the Group Policy Management Console of the Domain Controller is a setting that can Disallow this.
probably more info here;
This isn't correct. The ability to shutdown and restart a service is a permission at the server level. By default, Domain Admins are members of the local Administrators group on a server. The Administrators and Power Users groups on servers have the ability to shutdown services. While membership of these groups can be enforced by GPO, it's not a GPO setting or an AD setting that's giving out such permissions.
it is true. where a domain controller is present, local security policy will be overwritten by the domain security policy. i know that can be changed in some enviroments, but that is not the norm....
Sorry, Geoff, I'm going to have to disagree with you. I do happen to be a directory services/GPO administrator and architect. 🙂
There are two groups local to the Windows server that permit control of the services. Those two groups are administrators and power users. While you *can* control membership of those groups via GPO, there isn't a setting that you can pass via GPO to give certain users/groups the ability to control services *unless* you're doing it by controlling membership of these two groups. That's because there isn't a setting at the OS level to permit this. To check this for yourself, bring up your Local Security Policy. Navigate to Local Policies >> User Rights Assignment and Local Policies >> Security Options. While there are options to control who can shutdown the Windows server either logged on locally or remotely (different options), this isn't the same thing as controlling the services that are running on said Windows server.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply