My workstation that has SQL Server 2008 R2 Express for development purposes installed has been moved out of a domain into a Windows workgroup. While being part of the domain I used the account "MyDomain\MyUserName" that had domain admin rights. Now I log in to my computer as "MyComputerName\MyUserName" that has local admin rights.
In the SQL Server Configuration Manager I have changed the account that starts the "SQL Server (Express)" service from "MyDomain\MyUserName" to "MyComputerName\MyUserName".
I can connect with the instance in SQL Server Management Studio (using Windows authentication) and I can see all databases in the Object Explorer in the left side. But once I try to open any database node in the Object Explorer I get a message that access to the database is not possible. I get this message for all databases except system databases like "master" or "tempdb".
If I try to run any query on one of the databases I get a "SQL Server Message 916":
The server principal "MyComputerName\MyUserName" is not able to access
the database "MyDatabase" under the current security context.
Is there any way to the get access to my databases again?
Edit
I also tried to create a new database manually in SSMS but it throws the error:
CREATE DATABASE permission denied in database 'master' (Microsoft SQL
Server, Error: 262)
Edit 2
I forgot to mention that the workstation runs with Windows XP (Service Pack 3) and the domain the workstation has been removed from was an old Windows NT domain – in case this should be important to find the reason for the problem.
Best Answer
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