Sql-server – Assign most elevated permission to Sql Server DB

I have been in this situation before. If you do not have the sa password and you are not a member of the sysadmin group then you only have one option remaining to you which is to start SQL Server in maintenance mode. This will allow you to reset the sa password and to add yourself to the sysadmin server role.

Here is a link that I found useful when I had to do this as it describes setp-by-step what you need to do. Good luck!

http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

SQL Server manages its own security. Just because you have local Windows administrator rights doesn't automatically grant you access.

That said, there are two approaches to get back in, using local administrative rights:

1. Impersonate the NT AUTHORITY\SYSTEM account. There are two methods to do this, as I blogged about here. These methods allow you to leave the server running while recovering access. If there is no login for that system account, you have to use the second method (which is easier, and if you don't care about downtime, the one you should use anyway because it's safer).

2. Restart the database engine service in single user mode, which does grant local administrators sysadmin rights over the instance. Do what you need to do, then restart the instance in multi-user mode. This is the backdoor which was created precisely for the situation you're in; however, you do have to interrupt service to do it.