Yesterday I moved my machine from one domain to another - foolishly forgetting the implications for my local instance of SQL Server! Mixed Mode authentication is not enabled, and the only local account login has only "public" permissions.

SQL Server 2005 Service Pack 2 had a tool called the User Provisioning Tool for Windows Vista (sqlprov.exe) which allowed you to add Domain Users to a local SQL 2005 instance (it doesn't work against SQL 2008 btw) - my question is.. is there a similar tool for SQL Server 2008 or am I going to have to do a reinstall?

Also let me know if you think this belongs on StackOverflow


The solution was actually quite obvious now that I think about it. You have to fire up SQL Server in single user mode.

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

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

| improve this answer | |

In SQL 2008, if you haven't done set up a user to be in the sysadmin role during setup, then you're left with running it in single-user mode, which lets you connect to the DAC as a Windows administrator with escalated permissions. You can then assign someone/somegroup to the sysadmin role.

In SQL 2005, local admins have sysadmin access, but with so many non-DBAs have this access, this was sometimes frowned upon.

| improve this answer | |
  • Interesting, I didn't know about that - I've always assigned accounts during setup. – Rob Sanders Mar 8 '10 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.