Link to home
Create AccountLog in
Avatar of ovidbailey
ovidbaileyFlag for United States of America

asked on

Cannot get full control of SQL Server after host server disconnected from domain

I have a SQL 2008 server that is currently on a VM having been recovered from a physical disk.

The SQL server used to be part of a domain, but the DC for that domain no longer exists.

I am logging into the server using a local admin account. Using SQL Server Management Studio, I can connect to the server and display the databases. I can add and delete data from those database tables.

However, if I try to do anything more than simple data manipulation, I'm getting a msg, "The user does not have permission to perform this action." This includes right-clicking on the database name and trying to view Properties. A backup also fails.

In the Security section of the DATABASE, I can see BUILTIN\Administrators as a user; the only Database Role assigned is db_owner.

In the Security section of the server, I see Logins for BUILTIN/Administators and sa. The only server role granted to BUILTIN/Administrators is public. The sa account is granted sysadmin priviliges.

I do not have the sa password, and I get a perms error when I try to set it. My sense is that the db that I care about has sa authentication set on it, rather than Windows perms.

SSMS comes up fine. I tried a manual start from the command prompt, but I just get a dialog box with a red X and "OK".

How do I get full control of this instance?

Thanks.
SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of ovidbailey

ASKER

From rrjegan17's comment, I searched bing for "reset sql server sa password". First hit was for passwordunlocker.com. Even though they had a tool for changing the sa password, they also had the helpful instruction (Option 3) for the steps that I used:
http://www.passwordunlocker.com/knowledge/how-to-reset-sa-password-or-other-sql-password.html