none
Locked out of SQL Server 2008 R2 Express on Windows 7 RRS feed

  • Question

  • Locked out of SQL Server 2008 R2 Express on Windows 7

    I created a windows application that uses SQL Server 2008 R2 Express installed on the users local machine.

    One of my application's users performed their installation of SQL Express + database + application while logged into Windows 7 as DomainA\UserAccountA. All was good.

    I now need them to Restore a backup of the SQL Database.
    However... the user has since been moved to a new domain & user account on the same machine (they have been transferred to an outside organisation for a project who has granted them the new account in their domain). So now they login to the same Windows 7 machine as DomainB\UserAccountB

    But now when the SQL Server database Restore is attempted, they are getting permission errors: “User does not have permission to RESTORE database '[databaseName]'”

    I believe the error is due to Windows Authentication issues with the new account

    My initial thought was to temporarily log back into the machine with the original DomainA\UserAccountA and add DomainB\UserAccountB as a new Login with admin rights etc via SSMS, but the user cannot login to Windows with the original account. Its not clear if they have simply forgotten their password or did the ITAdmin person overwrite the original DomainA\UserAccountA when setting up the second account - whatever cause, it looks like we cannot log in with the original account.

    Sigh...

    Does anyone have any ideas re how to get around this?
    Would an Administrator be able to get around this?

    The only other thing I can think of is uninstall and re-install SQL Server 2008 R2 Express using the new account but is there any other way?

    Wednesday, October 12, 2011 9:49 PM

Answers

  • Gary,

    What you can do to get around this is start SQL Server up in single user mode while you are logged in as an administrator on that computer. Once started in single user mode, administrators of that machine will have sysadmin access when they connect, but please note that only one connection is allowed when the instance is in single user mode. Once connected you can use the CREATE LOGIN command to add new logins and then use sp_addsrvrolemember to add a server level role to them (for example sysadmin). Once finished you can close the connection and restart sql server normally.

    Single user mode Resource: http://msdn.microsoft.com/en-us/library/dd207004.aspx

    sp_Addsrvrolemember Resource: http://msdn.microsoft.com/en-us/library/ms186320.aspx

    -Sean

    • Proposed as answer by Crazy Adam Friday, October 14, 2011 1:30 AM
    • Marked as answer by GaryBarrett Friday, October 14, 2011 11:48 AM
    Thursday, October 13, 2011 12:25 PM

All replies

  • I created a windows application that uses SQL Server 2008 R2 Express installed on the users local machine.

    One of my application's users performed their installation of SQL Express + database + application while logged into Windows 7 as DomainA\UserAccountA. All was good.

    I now need them to Restore a backup of the SQL Database.
    However... the user has since been moved to a new domain & user account on the same machine (they have been transferred to an outside organisation for a project who has granted them the new account in their domain). So now they login to the same Windows 7 machine as DomainB\UserAccountB

    But now when the SQL Server database Restore is attempted, they are getting permission errors: “User does not have permission to RESTORE database '[databaseName]'”

    I believe the error is due to Windows Authentication issues with the new account

    My initial thought was to temporarily log back into the machine with the original DomainA\UserAccountA and add DomainB\UserAccountB as a new Login with admin rights etc via SSMS, but the user cannot login to Windows with the original account. Its not clear if they have simply forgotten their password or did the ITAdmin person overwrite the original DomainA\UserAccountA when setting up the second account - whatever cause, it looks like we cannot log in with the original account.

    Sigh...

    Does anyone have any ideas re how to get around this?
    Would an Administrator be able to get around this?

    The only other thing I can think of is uninstall and re-install SQL Server 2008 R2 Express using the new account but is there any other way?

    • Merged by Stephanie Lv Friday, October 14, 2011 1:27 AM duplicated thread
    Wednesday, October 12, 2011 9:47 PM
  • Gary,

    What you can do to get around this is start SQL Server up in single user mode while you are logged in as an administrator on that computer. Once started in single user mode, administrators of that machine will have sysadmin access when they connect, but please note that only one connection is allowed when the instance is in single user mode. Once connected you can use the CREATE LOGIN command to add new logins and then use sp_addsrvrolemember to add a server level role to them (for example sysadmin). Once finished you can close the connection and restart sql server normally.

    Single user mode Resource: http://msdn.microsoft.com/en-us/library/dd207004.aspx

    sp_Addsrvrolemember Resource: http://msdn.microsoft.com/en-us/library/ms186320.aspx

    -Sean

    • Proposed as answer by Crazy Adam Friday, October 14, 2011 1:30 AM
    • Marked as answer by GaryBarrett Friday, October 14, 2011 11:48 AM
    Thursday, October 13, 2011 12:25 PM
  • Hey Gary,

    If your only administrative login is a domain account to which you don't have access, you can get back in (so to speak), but the options are ugly.

    Option-1: Re-Installation (1-hour)

    Shutdown the instance. Copy the data (.mdf/.ndf) and log (.ldf) files for the databases you want to keep. Uninstall SQL Server 2008 R2 Express. Reinstall SQL Server 2008 R2 Express. Attached the data and log files for the database you want the instance to serve.

    Option-2: Re-build System Databases (1-hour)

    Check out the following blog post for guidence on using the Setup program to rebuild your system databases:

    How to Rebuild System Databases in SQL Server 2008
    http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

    As part of this exercise, you'll have to re-provision administrative users, which should get you back in. If you've applied any patches or service packs, you'll need to re-apply them.

    Option-3: Swap Master Databases (30-minutes)

    Microsoft CSS would never embrace this option, and I'm sure there are a half dozen reasons not to do this. That said, you could grab the data and log files of a master database from another instance, which has a login you can use to login with. If the database doesn't have a login you can use on the other person's machine, make one. Shutdown the SQL Server instance that is giving you problems. Swap in the data and log files from the other master database. Restart the instance. Clean-up the users.

    I've done this in the past on 2005 instances, and it has worked fine. Just keep in mind, you're off the reservation with this one.

    There are probably other options too. This is what I came up with on short notice.

    Good luck bro! You're close to a fix.

    ~CA


    Adam
    Thursday, October 13, 2011 12:44 PM
  • I believe that you can get this working, if you start SQL Server in single-user mode from the command prompt. (Make sure that you started the command-prompt window with Run as Administrator). Then use SQLCMD to add the user's new Windows login as administrator on the server. Then start the server in the regular fashion.

    See sqlservr under Command Prompt Utilities i Books Online for which option to use to run in single-user mode. (I seem to recall that it is -m).

    To make the user sysadmin, use sp_addsrvrolemember.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Crazy Adam Friday, October 14, 2011 1:30 AM
    Thursday, October 13, 2011 2:00 PM
  • Sean, great answer bro. I need to remember this.

    ~CA


    Adam
    Friday, October 14, 2011 1:34 AM