2

I have a local install of SQLExpress that I haven't touched in months. Now I need to use it, and I've forgotten the SA password.
That would be fine, except that my local user which IS a member of the local Administrators Group apparently doesn't have permission to change the SA password or permissions to change my own User Rights.

I have tried using SQL Server Management Studio, I have done Single User Mode with SQL Server and used sqlcmd, they all tell me I don't have permissions for it.

I'm afraid that even reinstalling SQL Express isn't going to fix this for me. Does anyone know of any other route I could take here?

James P. Wright
  • 8,991
  • 23
  • 79
  • 142
  • 1
    This question belongs on Database Administrators SE. –  Oct 03 '11 at 00:57
  • @Surfer513 : Agreed, didn't even know that one existed. StackExchange may be growing too large for its own britches. – James P. Wright Oct 03 '11 at 01:27
  • Ha! That is true. DBA stack exchange is great. As we all know, database administration is definitely a science within itself. Very fun stuff though. –  Oct 03 '11 at 01:32

2 Answers2

5

As long as you have physical access to the machine, there's a way to start SQL Server in maintenance mode, which will allow you to connect and reset the password, assign new permissions, etc. Instructions.

Short version:

  1. From the Start menu, open SQL Configuration Manager.
  2. Go to the "SQL Server Services" node.
  3. Right-click on the SQL Server instance and select Properties.
  4. Under "Advanced", find the "Startup Parameters" setting, and prepend -m; (that is, add a hyphen, m, and semicolon to the beginning of the existing string).
  5. OK out.
  6. Stop and restart SQL Server.

You're now in maintenance mode, and can connect locally and change whatever settings you need to, including permissions. (The link says you can only connect with sqlcmd, but I know that, with SQL 2008, you can connect with SQL Server Management Studio.)

Make sure to take the server back out of maintenance mode (remove the -m;) when you're done, so that apps can connect normally.

Joe White
  • 94,807
  • 60
  • 220
  • 330
  • This is the process I went through for "Single User Mode" and when I used sqlcmd to try to add my user to the sysadmins group I got the "you do not have permission" message....which is where I started to abandon hope and am praying a reinstall fixes it. – James P. Wright Oct 02 '11 at 23:32
  • Did you make sure to stop and restart SQL Server after you made the configuration change? – Joe White Oct 03 '11 at 00:30
  • Yep. I think there may be some weird random issues with being on my local admin group. It works for some things and for others it doesn't. :\ – James P. Wright Oct 03 '11 at 01:27
1

I would just uninstall and then reinstall -- it's not a very lengthy process. I believe you will be presented with the option to setup your sa account on the new installation.

Jeff Ogata
  • 56,645
  • 19
  • 114
  • 127