When working with SQL Server Express 2008 R2, you might encounter a situation where neither the standard user account nor the local Windows Administrator has sufficient privileges. This typically happens when:

• The BUILTIN\Administrators group was removed during installation
• The sa password was lost or never documented
• Previous administrators left without proper handover

To re-enable Windows Administrator access, you'll need to start SQL Server in single-user mode:

NET STOP MSSQL$SQLEXPRESS
NET START MSSQL$SQLEXPRESS /mSQLCMD

Then connect using SQLCMD:

SQLCMD -S .\SQLEXPRESS
1> CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS;
2> GO
1> EXEC master..sp_addsrvrolemember 'BUILTIN\Administrators', 'sysadmin';
2> GO
1> EXIT

Restart SQL Server normally:

NET STOP MSSQL$SQLEXPRESS
NET START MSSQL$SQLEXPRESS

If you need to recover SA access instead, follow these steps in single-user mode:

SQLCMD -S .\SQLEXPRESS
1> ALTER LOGIN sa WITH PASSWORD = 'YourNewStrongPassword123!';
2> GO
1> ALTER LOGIN sa ENABLE;
2> GO
1> EXIT

For systems where single-user mode isn't practical, try the DAC:

SQLCMD -S ADMIN:\SQLEXPRESS -U sa -P OldPassword
1> -- If you know current password but need to change it:
2> ALTER LOGIN sa WITH PASSWORD = 'NewSecurePassword456!'
3> GO

Once you regain access, implement these best practices:

• Document all administrative credentials in secure password manager
• Create named administrator accounts instead of relying on BUILTIN\Administrators
• Set up proper database roles for regular operations

Recently, while managing a client's SQL Server Express 2008 R2 instance, I encountered an issue where the usual database creation process failed due to insufficient permissions. The standard approach of using the local BUILTIN\Administrator account didn't work because newer versions of SQL Server Express no longer include this login by default.

SQL Server Express can operate in two authentication modes:

• Windows Authentication Mode: Relies solely on Windows accounts
• Mixed Mode: Allows both Windows and SQL Server authentication (including the SA account)

In our case, the installation was using Windows Authentication only, which complicated access when the local admin account wasn't available.

If you need to add the local administrator account back:

USE [master]
GO
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC sp_addsrvrolemember 'BUILTIN\Administrators', 'sysadmin'
GO

When you need to regain access via the SA account, follow these steps:

-- Step 1: Start SQL Server in single-user mode
NET STOP MSSQL$SQLEXPRESS
NET START MSSQL$SQLEXPRESS /m"SQLCMD"

-- Step 2: Connect and reset password
sqlcmd -S .\SQLEXPRESS
1> ALTER LOGIN sa WITH PASSWORD = 'YourNewPassword';
2> GO
1> EXIT

-- Step 3: Restart normally
NET STOP MSSQL$SQLEXPRESS
NET START MSSQL$SQLEXPRESS

For automated environments, PowerShell can be more convenient:

# Reset SA password using SMO
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$server = New-Object Microsoft.SqlServer.Management.Smo.Server("(local)\SQLEXPRESS")
$server.Logins["sa"].ChangePassword("NewSecurePassword123!")
$server.Logins["sa"].Alter()

Remember these important security practices:

• Always use strong passwords for SA accounts
• Document credentials in a secure password manager
• Consider using Windows Authentication where possible
• Regularly review and audit SQL Server logins

If you encounter problems:

• Ensure the SQL Server service account has sufficient privileges
• Verify the instance name is correct (especially for named instances)
• Check the SQL Server error logs for authentication failures