Many times the DBA is faced with the dilemma of granting excessive access to an application or user so it can ‘just work’.
This can cause a lot of problems. There are commands that can be given from T-SQL that can do a lot of damage to your systems and reputation.
We’re not talking about simple things like drop table, truncate, etc.
Did you know there is a T-SQL command to shutdown the current instance the query is executed against?
If you grant sysadmin or serveradmin access to an application or user, they can simple shutdown your SQL Server using T-SQL.
Wait… what? What can they do?
The command is really simple:
SHUTDOWN
Yepp, that’s it.
As we know already, there are other ways to shutdown SQL Server.
You can:
[list_check] [li]Use the SQL Server Configuration Manager[/li] [li]Run net stop mssqlserver from a command prompt, or net stop mssql$instancename [/li] [li]By using services in control panel[/li] [/list_check]
Okay, so what does SHUTDOWN actually do?
If you just issue the SHUTDOWN command, SQL Server does the following:
[list_ordered] [li]Disable logins (except for sysadmin and serveradmin)[/li] [li]Wait for current transactions to complete[/li] [li]insert a checkpoint in every database[/li] [/list_ordered]
But wait! There’s more!
Let’s make it even worse! Check this syntax out:
SHUTDOWN WITH NOWAIT
Yeah, you read that right. You can add WITH NOWAIT to the command! As if it wasn’t bad enough to begin with!
When you add WITH NOWAIT you are telling SQL Server to shutdown without checkpoints in every database. This means when you restart the service all uncompleted transactions will rollback.
FUN for everyone!
BONUS!
Oh, just so you know. If you run your SQL Server from the command prompt (sqlserver.exe), CTRL+C does the same as a SHUTDOWN WITH NOWAIT.
[divider_top][icon_box icon=”users” title=”Who is Chris Bell?”]
Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com. Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services along with the destination location week-long training series: SQL Summer Camp.
Click here to contact Chris or to follow him on twitter. [/icon_box]