Enabling super single user mode with SQL Server
I recently got an email from a fellow MVP about single user mode. It made me think about some features I had just been looking at and so I started playing.
The annoyance about single user mode for SQL Server is that its not really single user, but more like single connection mode.
So how can you get round it, well there is extension to the -m startup option that allows you to specify an application name, and only connections with that application name can connect. This is very useful if you have sharepoint or reporting services running as they often get the single connection and that leaves you without a connection.
However this approach still only restricts the single user connection to a specific connection with a specific application name.
What if you want to be able to use management studio, that uses multiple connections.
Well the answer is logon triggers. You can implement a logon trigger that checks some property of the connection and rejects if it isn’t valid. Something like this
create trigger tr_login
on all server
for logon
as
begin
if APP_NAME() <> 'Simons super app'
begin
rollback;
end
end
You can now only connect if the application name of the connection string is “Simons super app”.
How do you do that in management studio? its easy you just set an additional connection property when you connect and heh presto.
To get to the Additional Connection Parameters tab you need to click on the options button on the first screen.
This isn’t a security feature as anyone can set the application name. It might however be useful if you are doing an upgrade or something else and you wish to stop people connecting.