Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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).

share|improve this answer
    
Hi Rob, please accept this answer to close the question. –  user3914 Mar 8 '10 at 12:55
    
Yeah, I have to wait two days to accept my own answer.. –  Rob Sanders Mar 8 '10 at 13:44

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.

share|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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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