How to recover SQL Server ownership when NT account is not valid anymore

Question

I have a local SQL Server Express 2005, for which I don't know the SA password. I always connected with my NT login and I used that when I created a DB I want to access now.

My company split and I am a member of a new domain now. There are no connections with the old domain anymore. My account resides in the new user domain and is a local administrator on the computer where SQL Server runs.

I can access the Server through the Management Studio, but not my user DB anymore. I am not recognized as a Server Admin anymore either. Obviously, local NT administrators are not automatically mapped to be SQL Admins.

Is there a way to do that at this stage? Can I somehow recover access to the SA level of access?

Thanks in advance.

Answer 1

Not having tried this myself, but running the database in single user mode should give you SQL Server Admin privileges if you are a Windows Administrator on that local machine.

SQLServr.Exe –m

Found a technet post that describes the full procedure: http://blogs.technet.com/b/sqlman/archive/2011/06/14/tips-amp-tricks-you-have-lost-access-to-sql-server-now-what.aspx

Answer 2

you may find this solution elegant: https://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/#comments For me it did not work with SQL2016 but as the author states, it worked for him in older versions of MS SQL. Quotes (from the above link): Thanks to Mark Russinovich of Sysinternals fame, there is a very painless way to solve this problem without any downtime: PsExec. While it wasn't one of its primary design goals, PsExec allows you to run programs as the NT AUTHORITY\SYSTEM account, which - unlike "regular" Administrator accounts - has inherent access to SQL Server. PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe" Now, you can go in to Security > Logins and add your account as a sysadmin, add other admin accounts, update the sa password, and do anything else you need to do to make your instance manageable. As you can see, I was able to connect in this way to both SQL Server 2008 and SQL Server 2012 instances from an instance of Management Studio 2012.