mssql :: create db users without knowing sa password

So, a client comes in, installs their app on our database server and leaves. It’s all good, everything works, but I want to see the database…They changed our sa password so I cant meddle with the database. Now forgive me, but this is my server, not yours, I own the data, I own the hardware so its mine. But what to do when you cant get into the server to add a user..simple.
Firstly, fire up sql server configuration manager and set a start up parameter of -m to your sql instance. This sets it to single user mode.
Restart the service and then open up a command prompt and fire up the SQLCMD command:

C:\Windows\system32>sqlcmd -S bdt137sa\sql2012

Now, run something resembling the following :

CREATE LOGIN foobar WITH PASSWORD = 'BlueCheese1';
USE master;
CREATE USER foobar FOR LOGIN foobar;

Now, go back and remove the -m flag, taking it out of single user mode, and connect using management studio with your new user [foobar;BlueCheese1].

….aaand your’e in.

Next time, watch over that pesky client next time he’s playing with your server!


Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>