-1

We are attempting to do an internal SQL backup of our databases, however it appears when the databases were configured the SA user account had been disabled. For whatever reason the BUILTIN\Users only have "public" permissions.

We have tried logging in with the "Administrator" account in the hopes to enable the SA account, however the Administrator user does not have permissions to do so (have also tried CLI to reset, did not work)

The reason we are doing this backup is the database is migrating to a new server, does anyone know of a way to re-enable the SA account, or to backup our database?

P.S this is a domain controller server.

Thanks!

user3045068
  • 51
  • 1
  • 8

1 Answers1

4

<Getting this out of the way first.>It's generally not recommended to put SQL on a DC. (I know, you probably inherited it that way, moving right along.)</Getting this out of the way first.>

You can indeed do this if you're willing to take SQL down.

  1. Shut down SQL in Services.
  2. Start an administrative command prompt.
  3. Navigate to the folder with the SQL executables.
  4. Start SQL Server in single user mode (sqlsevr -m -SSQLEXPRESS).
  5. Connect to the instance with SQLCMD and integrated security (SQLCMD -E -SSQLEXPRESS).
  6. Create an account for yourself if it doesn't already exist (Create Login [YourDomain\YourUsername] from windows; go).
  7. Make your account an administrator (exec sp_addsrvrolemember @loginname='YourDomain\YourUsername'; go).
  8. Restart SQL and connect.

You should then be able to re-enable SA, back up databases, etc.

(It's also possible that the local system account has backup privileges, but you probably want to regain control of your SQL Server anyway.)

Katherine Villyard
  • 18,550
  • 4
  • 37
  • 59
  • Sorry, I also forgot to mention that this is SQL 2008 Express, will that affect this procedure? – user3045068 Jan 09 '15 at 22:48
  • Nope. It'll work on either. It probably does have an instance name of \SQLEXPRESS; services knows for sure. – Katherine Villyard Jan 09 '15 at 22:49
  • Awesome, thanks. I've hit a bit of a snag however. There might be a problem with the installation of SQL, as when attempting to start in single user mode we're getting an error "Your SQL server installation is either corrupt or been tampered with.." – user3045068 Jan 09 '15 at 22:57
  • Did you use `SQLCMD -E -S.\SQLEXPRESS`, or whatever the instance name is? – Katherine Villyard Jan 09 '15 at 23:00
  • I was getting the error message on step 4. Was not able to start the sqlserver in single user mode. But if I try skipping it says the server is not accessible (probably because of services) – user3045068 Jan 09 '15 at 23:03
  • Still no luck :( there might just be something wrong with the installation, it wouldn't surprise me. – user3045068 Jan 09 '15 at 23:43
  • Try this and see if it helps. http://blog.sqlauthority.com/2009/02/10/sql-server-start-sql-server-instance-in-single-user-mode/ – Katherine Villyard Jan 09 '15 at 23:48
  • Just edited the answer with what I think is better syntax, too. (No space after -S.) – Katherine Villyard Jan 09 '15 at 23:51