locked
Unauthorized SQL Server Account can shut down sql server RRS feed

  • Question

  • SQL Server 2008 R2 RTM build (Standard Edition)

    SQL Server account has following rights as per select * from fn_my_permissions(null, 'server')
    server CONNECT SQL
    server VIEW 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
    Thursday, October 28, 2010 11:38 AM

All replies

  • There are two ways you can shutdown SQL Server.

    1. You can execute the SHUTDOWN Transact-SQL statement. In this case, SQL Server is shutting down itself. This statement uses the SQL Server permissions.

    2.Or you can shutdown SQL Server (and any other service) using the Windows service control. Then Windows initiates the shutdown, not SQL Server. This method requires a Windows permission, not a SQL Server permission. That's what you are doing.  Management Studio is asking Windows to stop the Database Engine service. This is equivilant to using the SQL Server Configuration Manager or the Windows services.msc application. All members of the local Administrators group on the computer have the permission to stop services. That's probably why it's working for you. Permission stop/start/pause a service can be granted separatly to a Windows user, though that's complicated.

     


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Thursday, October 28, 2010 3:57 PM