Question

Restoring SA access to SQL Server

SQL Server, System administration, Permissions

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.

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.