Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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.

share|improve this question
What does it give you if you do this query? SELECT @@SERVERNAME AS 'Server Name'; – benRollag Aug 4 '12 at 18:34
@benRollag: It returns MyComputerName\SQLEXPRESS. – Tom Aug 4 '12 at 18:41

2 Answers

up vote 1 down vote accepted

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

share|improve this answer
It worked!!! The procedure described in the blog post was the solution. I can access all databases again. Thank you very much, you saved the rest of my weekend! – Tom Aug 5 '12 at 11:34
up vote 0 down vote

The service account you use to run SQL server (what you enter in configuration manager) must also be a member of the sysadmin role within SQL server. If you have not done so already, log in using the sa account and grant the Windows service account the sysadmin server role.

share|improve this answer
But how can I log in as sa? I've never used SQL Server authentication, only Windows authentication and when I want to login to SSMS using Windows authentication I cannot enter a user name, the field is grayed out with the name of the user I am currently logged into Windows (MyComputerName\MyUserName). – Tom Aug 4 '12 at 21:29
I tried to change the authentication mode from Windows to SQL Server following the instructions from here: msdn.microsoft.com/en-us/library/ms188670%28v=sql.105%29.aspx But I can't, SSMS is telling me (in step 2) that I don't have the permission to change the mode. – Tom Aug 4 '12 at 21:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.