Deny user to start and stop SQL services

Question

Deny user to start and stop SQL services

RaviShankar1234

SSCommitted

Points: 1647
More actions
December 17, 2010 at 1:28 pm

#230894

Hi,
Recently in our team a new developer has joined I was given a task of allocating creating the user and restrict him from starting and stopping of SQL Server Services through SSMS. Can any one let me know please.
Thanks,
Ravi

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

only a sysadmin has the ability to start and stop the server via SSMS; so do not give their login sysadmin and you are all set; just make them a user, and decide which databases they will get access to.

now if they have the ability to rdp to the servers desktop, they might be able to start and stop via administrative Tools>>Services

but that is network/local admin permissions vs SQL permissions.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Geoff A SSChampion Points: 11511 More actions · Answer 2

the ability to control SQL services via SSMS has nothing to do with SQL permissions.

it is based on windows level permissions. local security and domain security policies.

users in the Administrators or Power Users group will be able to stop and start via SSMS if they can access any of the database on the server.

Lowell SSC Guru Points: 323518 More actions · Answer 3

Geoff A (12/21/2010)
the ability to control SQL services via SSMS has nothing to do with SQL permissions.
it is based on windows level permissions. local security and domain security policies.
users in the Administrators or Power Users group will be able to stop and start via SSMS if they can access any of the database on the server.

um...no....I don't think that is correct....at least if you are talking about using the SSMS inteface to start and stop the server.

the groups you mentioned often inherit the sysadmin priviledges that i mentioned above.

windows level permissions, local security and domain security policies all have more to do with the ability to stop and start the services from outside of SSMS, I believe.

I'm pretty sure I can log into Windows as a read only user, call SSMS as sa or some other sysadmin, and start and stop the server via SSMS. If you can show I'm incorrect, I'd be glad to learn from this experience.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Geoff A SSChampion Points: 11511 More actions · Answer 4

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.

Lowell SSC Guru Points: 323518 More actions · Answer 5

SSMS uses the extended stored procedures master.dbo.xp_sqlagent_monitor and master.dbo.sys.xp_servicecontrol to stop and start the services for the agent or the service on the server.

those procs are called without regard to any domain permissions. you can google that a bit and see basically what i saw.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Geoff A SSChampion Points: 11511 More actions · Answer 6

Lowell (12/21/2010)

Geoff A (12/21/2010)
I'm pretty sure I can log into Windows as a read only user, call SSMS as sa or some other sysadmin, and start and stop the server via SSMS. If you can show I'm incorrect, I'd be glad to learn from this experience.

try this Lowell,

log into a SQL server as sa or some other sysadmin account from your desktop with SSMS where the SQL Server in question is on another domain where you are not a domain admin, power user or local system admin.

you will be able to to do SQL tasks, but you will not be able to stop, start or restart SQL.