Adding Logins to SQL Server 2008 (User Provisioning) 1


Today I learnt an important lesson about moving a PC from one Active Directory domain to another – the process will strip (domain) logins from SQL Server!  Yes, I know this is kind of obvious and expected, but also easy to overlook.

So our scenario is that we don’t have any Windows Accounts which can authenticate to SQL Server (via SQL Management Studio or via command line tools) and/or we have no accounts as members of the sysadmin role.  the “sa” account is not usable because mixed mode authentication is not enabled.  We can’t administer the SQL instance.

This is a big problem if you rely on Windows Authentication (i.e. no mixed mode authentication), and you haven’t got any local (built in or otherwise) accounts which have been assigned sysadmin permissions. 

You’re basically stuck!  You can’t log onto the instance and you can’t administer SQL Server, even locally.  You can’t use the Dedicated Administration Console (DAC) either since it requires the active user to be in the sysadmin role!

SQL Server 2005 Service Pack 2 came with a handy utility called the User Provisioning Tool for Windows Vista (sqlprov.exe) and allowed you to assign sysadmin logins for your account (and other accounts).  This tool unsurprisingly doesn’t work with SQL Server 2008, and as far as I can tell there is no equivalent tool available (please correct me if I am wrong).

At this point you might be reaching for the installation media for a reinstall, but never fear – there is a solution.  The solution was actually quite obvious now that I think about it. You have to start SQL Server in single user mode.

This allows you to log on (using SSMS) from the localhost with sysadmin permissions. From there, you can then create new logins (including domain account logins, if so desired).

Here’s how to start SQL Server in single user mode and here’s more information on how to start SQL Server from the command prompt (which you will have to do for single user mode).

Big thanks to Rob Farley for supplying the suggestions.


About Rob Sanders

IT Professional and TOGAF 9 certified architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.


Leave a comment

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=""> <s> <strike> <strong>

One thought on “Adding Logins to SQL Server 2008 (User Provisioning)

  • Shayla

    It’s a pity you don’t have a donate button! I’d definitely donate
    to this superb blog! I guess for now i’ll settle for bookmarking and adding
    your RSS feed to my Google account. I look forward to brand new updates and
    will share this website with my Facebook group. Talk soon!

    Here is my web page :: golf galaxy (Shayla)