Restoring SA access to SQL Server

Tags:
Permissions
SQL Server
System administration
I have somehow removed my SA permissions from my SQL server. I do not know the SQL SA password. Is there any other way to restore my SA access to SQL? I am a local admin on this server.

Answer Wiki

Thanks. We'll let you know when a new response is added.

By default all members of the Administrators group will be members of the sysadmin fixed server role.

If however you have removed the BUILTIN\Administrators group fro the sysadmin fixed server role you have a couple of things to check.

1. If the server is setup for mixed mode authentication log into the SQL Server using the sa account. The password will be the password you set when you installed the SQL Server.

2. If another login is setup as a member of the sysadmin fixed server role, connect with this account and restore your login rights to the database.

3. If you are using SQL 2000 you can rebuild the master database by using the rebuildm.exe utility which comes with SQL Server 2000. After using the rebuild.exe you will need to recreate all the logins for you users and applications, as well as attach any user databases which you have created on the server.

4. If you have a backup of the master database from before you lost the permissions you can restore this master database to another SQL Server, then stop both the SQL Server you restored the master database to, as well as the database you need to fix, and copy the physical files which make up the master database to the server which the incorrect rights. When you restart the SQL Server some of your user databases may show suspect. You can use the ALTER DATABASE command to bring them back online, or detach them and reattach them.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following