我在 Windows 10 Home 上安装了 MSSQL Server 2016，并创建了一些数据库。然后我升级到了 Windows 10 Pro，现在由于我以其他用户身份登录 SQL（旧用户是本地用户，新用户是域用户），它不允许我访问我的数据库 - 我无法指定用户访问它们，我无法打开它们，也无法删除它们。
我努力了：
- 创建新的 SQL 用户并分配权限
- 以本地用户身份登录 Windows（已被删除；并且无法在本地计算机上重新创建）
- 使用域管理员用户分配权限（在管理工作室中）
我不知道sa凭证。
我如何访问我的数据库？
答案1
这是另一种可能帮助你访问的方法SQL 服务器实例Windows 10机器；我没有用过这个，但它可能也能用。
重置忘记的 SA 密码
- 点击开始， 指向跑步并输入命令，按下Enter键。
将出现命令提示符。运行以下命令：
Osql –S john –E将 john 替换为您的实际计算机名称。
然后键入此命令来更改您忘记的 SA 密码。
EXEC sp_password NULL, ’123456’, ’sa’将 123456 替换为您想要的密码。
类型去使更改生效。
现在您可以使用新密码登录 SA 帐户！
您也可以使用此方法更改其他用户帐户的 SQL Server 密码。如果您仍然无法重置 SA 密码或 SA 帐户被锁定或禁用，请查看此文章：解锁 SQL Server SA 帐户的 2 种方法。
答案2
SQL Express 批处理脚本：将账户添加到 SQL Server SysAdmin 角色
尝试此脚本，看看它是否添加了您以 sysadmin 权限执行该脚本的用户帐户，以访问 Windows 计算机上的 SQL Server 实例。这需要通过重命名文本文档将其保存到或.cmd文件.bat，然后需要将其作为批处理脚本执行。
我从 GitHub 上找到这个“将当前用户添加到 SQL Server 中的 sysadmin 角色的脚本”几年前，我曾将其用于 SQL Express 安装，以授予系统管理员对服务器上本地管理员帐户的访问权限。我确认它有效后保存了它 — 所以我在这里与其他人分享它。
@echo off
::
:: ****************************************************************************
::
:: Copyright (c) Microsoft Corporation. All rights reserved.
:: This code is licensed under the Microsoft Public License.
:: THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
:: ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
:: IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
:: PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
::
:: ****************************************************************************
::
:: CMD script to add a user to the SQL Server sysadmin role
::
:: Input: %1 specifies the instance name to be modified. Defaults to SQLEXPRESS.
:: %2 specifies the principal identity to be added (in the form "<domain>\<user>").
:: If omitted, the script will request elevation and add the current user (pre-elevation) to the sysadmin role.
:: If provided explicitly, the script is assumed to be running elevated already.
::
:: Method: 1) restart the SQL service with the '-m' option, which allows a single connection from a box admin
:: (the box admin is temporarily added to the sysadmin role with this start option)
:: 2) connect to the SQL instance and add the user to the sysadmin role
:: 3) restart the SQL service for normal connections
::
:: Output: Messages indicating success/failure.
:: Note that if elevation is done by this script, a new command process window is created: the output of this
:: window is not directly accessible to the caller.
::
::
setlocal
set sqlresult=N/A
if .%1 == . (set /P sqlinstance=Enter SQL instance name, or default to SQLEXPRESS: ) else (set sqlinstance=%1)
if .%sqlinstance% == . (set sqlinstance=SQLEXPRESS)
if /I %sqlinstance% == MSSQLSERVER (set sqlservice=MSSQLSERVER) else (set sqlservice=MSSQL$%sqlinstance%)
if .%2 == . (set sqllogin="%USERDOMAIN%\%USERNAME%") else (set sqllogin=%2)
:: remove enclosing quotes
for %%i in (%sqllogin%) do set sqllogin=%%~i
@echo Adding '%sqllogin%' to the 'sysadmin' role on SQL Server instance '%sqlinstance%'.
@echo Verify the '%sqlservice%' service exists ...
set srvstate=0
for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
if .%srvstate% == .0 goto existerror
:: elevate if <domain/user> was defaulted
if NOT .%2 == . goto continue
echo new ActiveXObject("Shell.Application").ShellExecute("cmd.exe", "/D /Q /C pushd \""+WScript.Arguments(0)+"\" & \""+WScript.Arguments(1)+"\" %sqlinstance% \""+WScript.Arguments(2)+"\"", "", "runas"); >"%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js"
call "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js" "%cd%" %0 "%sqllogin%"
del "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js"
goto :EOF
:continue
:: determine if the SQL service is running
set srvstarted=0
set srvstate=0
for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
if .%srvstate% == .0 goto queryerror
:: if required, stop the SQL service
if .%srvstate% == .1 goto startm
set srvstarted=1
@echo Stop the '%sqlservice%' service ...
net stop %sqlservice%
if errorlevel 1 goto stoperror
:startm
:: start the SQL service with the '-m' option (single admin connection) and wait until its STATE is '4' (STARTED)
:: also use trace flags as follows:
:: 3659 - log all errors to errorlog
:: 4010 - enable shared memory only (lpc:)
:: 4022 - do not start autoprocs
@echo Start the '%sqlservice%' service in maintenance mode ...
sc start %sqlservice% -m -T3659 -T4010 -T4022 >nul
if errorlevel 1 goto startmerror
:checkstate1
set srvstate=0
for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
if .%srvstate% == .0 goto queryerror
if .%srvstate% == .1 goto startmerror
if NOT .%srvstate% == .4 goto checkstate1
:: add the specified user to the sysadmin role
:: access tempdb to avoid a misleading shutdown error
@echo Add '%sqllogin%' to the 'sysadmin' role ...
for /F "usebackq tokens=1,3" %%i in (`sqlcmd -S np:\\.\pipe\SQLLocal\%sqlinstance% -E -Q "create table #foo (bar int); declare @rc int; execute @rc = sp_addsrvrolemember '$(sqllogin)', 'sysadmin'; print 'RETURN_CODE : '+CAST(@rc as char)"`) do if .%%i == .RETURN_CODE set sqlresult=%%j
:: stop the SQL service
@echo Stop the '%sqlservice%' service ...
net stop %sqlservice%
if errorlevel 1 goto stoperror
if .%srvstarted% == .0 goto exit
:: start the SQL service for normal connections
net start %sqlservice%
if errorlevel 1 goto starterror
goto exit
:: handle unexpected errors
:existerror
sc query %sqlservice%
@echo '%sqlservice%' service is invalid
goto exit
:queryerror
@echo 'sc query %sqlservice%' failed
goto exit
:stoperror
@echo 'net stop %sqlservice%' failed
goto exit
:startmerror
@echo 'sc start %sqlservice% -m' failed
goto exit
:starterror
@echo 'net start %sqlservice%' failed
goto exit
:exit
if .%sqlresult% == .0 (@echo '%sqllogin%' was successfully added to the 'sysadmin' role.) else (@echo '%sqllogin%' was NOT added to the 'sysadmin' role: SQL return code is %sqlresult%.)
endlocal
pause