logo标签列表

如何将本地SQL Server实例的管理员访问权限授予自己？

sqlsql-serversql-server-2008ssmssqlcmd
113

我在本地计算机安装了SQL Server 2008 R2。但是，由于权限不足，我无法创建新数据库。

“CREATE DATABASE PERMISSION DENIED”

所以，我尝试将管理员权限分配给我的当前登录

“用户没有执行此操作的权限。”

我还尝试创建一个新的登录名，该登录名将具有管理员权限，但没有成功。我如何授予自己管理员权限以便可以创建数据库？ 我可以重新安装，但我不想这样做。

1
您是尝试通过 SQL Server 身份验证还是 Windows 身份验证进行连接？ - Darren
Windows身份验证（见标题）。我成功连接了。错误来自尝试执行管理员任务。 - SkonJeet
9个回答
116

打开命令提示符窗口。如果您已经运行了默认实例的 SQL Server，请在命令提示符上运行以下命令以停止 SQL Server 服务：

net stop mssqlserver

现在进入安装 SQL Server 的目录。该目录可以是以下其中之一：

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn

找到你的 MSSQL 目录，像这样 CD 到它：

CD C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

现在运行以下命令以单用户模式启动 SQL Server。由于指定了 SQLCMD，因此只能建立一个 SQLCMD 连接（来自另一个命令提示符窗口）。

sqlservr -m"SQLCMD"

现在，使用与上面以单用户模式启动SQL Server的相同用户身份打开另一个命令提示符窗口，并在其中运行：

sqlcmd

按下回车键。现在，您可以对以单用户模式运行的SQL Server实例执行SQL语句：

create login [<<DOMAIN\USERNAME>>] from windows;

-- For older versions of SQL Server:
EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAIN\USERNAME>>', @rolename = N'sysadmin';

-- For newer versions of SQL Server:
ALTER SERVER ROLE [sysadmin] ADD MEMBER [<<DOMAIN\USERNAME>>];

GO

来源.

更新：在 ALTER SERVER ROLE [sysadmin] ADD MEMBER [<<DOMAIN\USERNAME>>] 后不要忘记加分号，且不要在 GO 后添加额外的分号，否则该命令将无法执行。

2
快速提醒，sp_addsrvrolemember已被ALTER SERVER ROLE所取代。例如：ALTER SERVER ROLE [sysadmin] ADD MEMBER [domain\username]。 - Fermin
1
不要忘记在结尾处添加 GO;。 - NMrt
2
鉴于我们中的许多人现在正在运行没有SSMS的SQL Server，我认为这是一个更好的答案。 - Chaim Eliyah
1
谢谢您，不要忘记使用"net start mssqlserver"或SQL Server配置管理启动服务。 - Michael Seltene
1
谢谢您。对于其他用户：请注意，如果您有一个命名的 SQL 安装，请在调用 sqlservr 时使用 -s。 - Paolo
76

是的 - 看起来您在安装 SQL Server 时忘记将自己添加到 sysadmin 角色中。如果您是计算机上的本地管理员，此篇博客文章 可以帮助您使用 SQLCMD 将您的账户加入 SQL Server 的 sysadmin 组，而无需重新安装。这在我的观点中是 SQL Server 中的一个安全漏洞，但在这种情况下可以帮助您。

21
这不是一个安全漏洞，本地管理员需要拥有访问 SQL Server 的权限。实际上，本地管理员可以通过将数据文件复制到另一台机器上（然后在需要时再复制回来）来访问 SQL Server 的数据，因此你唯一的解决办法就是仅限制管理员权限给那些应该拥有它的人。 - yoel halb
这比卸载/重新安装要容易？几乎不可能！在我的情况下，我们被迁移到了另一个域; 所有现有的登录都变得毫无意义。 - dudeNumber4
9
链接已失效（仅包含链接的回答不受欢迎）。 https://dev59.com/rWkw5IYBdhLWcg3wbqKZ#9889484提供了与原博客文章相同的说明。 - janv8000
@yoelhalb 我尊重但坚决不同意你的观点。本地管理员不应该拥有 SQL Server 的管理员访问权限。 - Asher
@Asher 根据定义，本地管理员对本地系统上的一切都有完全访问权限。如果有一个正在运行的 SQL 服务器，那也在范围之内。如果你有一个不应该具有 SQL 服务器管理员访问权限的用户，那么他们就不应该是本地管理员，并且你应该相应地设置他们的访问权限和权限。 - Bryan
48

我采用了一个 SQL 2012 数据库，其中我不是系统管理员，但在该计算机上是管理员。我使用“以管理员身份运行”的方式打开 SSMS，将我的 NT 帐户添加为 SQL 登录账户，并将服务器角色设置为 sysadmin。没有问题。

16
这个解决方案对我有效，只需要添加一个额外的步骤： 必须使用“-m”标志在单用户模式下启动SQL Server。 这可以通过“SQL Server配置管理器”完成，右键单击服务器实例并选择属性，转到“启动参数”选项卡并添加“-m”。 - maets
1
绝对是所有答案中最快和最好的。 - Johnny
1
这实际上是正确的答案。需要注意的是，要将 SQL 设置为单用户模式，我必须运行 >net stop MSSQL$SQLEXPRESS，然后再运行 >net start MSSQL$SQLEXPRESS /m。 - Ev.
这个答案加上@maets的评论就是正确的答案。你必须添加-m参数以管理员身份运行单用户模式，然后你就可以做所有的事情了！ - Thiago Silva
另一个提示：如果您无法作为单用户登录，请检查SQL Agent服务是否正在运行。它将首先“跳入”并阻止您的访问。关闭Sql Agent服务，以单用户模式启动服务器，然后将自己添加为sysadmin。更多详细信息请参见此处：https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out - Michael Blackburn
显示剩余3条评论
31

以下是一个声称能够解决此问题的脚本。

使用这个简单的脚本获取本地 SQL Server Express 的管理员权限

脚本下载链接

描述

此命令脚本允许您轻松将自己添加到本地 SQL Server 实例的 sysadmin 角色中。您必须是 Windows 本地 Administrators 组的成员，或者拥有 能够访问此类用户凭据的权限。该脚本支持 SQL Server 2005 及更高版本。

如果您是开发人员，试图使用他人安装的 SQL Server 2008 Express， 那么该脚本最为实用。在这种情况下，通常情况下您不会具有 SQL Server 2008 Express 实例的管理员权限，因为默认情况下只授予安装 SQL Server 2008 的人管理员权限。

安装 SQL Server 2008 Express 的用户可以使用 SQL Server Management Studio 将必要的特权授权给您。但是，如果未安装 SQL Server Management Studio 会怎样呢？ 或者更糟糕的是，如果安装用户不再可用呢？

此脚本可以在几个单击中解决问题！

注：您需要为 BAT 文件提供“实例名称”（可能将是 “MSSQLSERVER” - 但也可能不是）。您可以通过首先在“Microsoft SQL Server 管理控制台”中运行以下命令来获取该值：

 SELECT @@servicename

然后将结果复制，以便在BAT文件提示“SQL实例名称”时使用。

  @echo off 
    rem 
    rem **************************************************************************** 
    rem 
    rem    Copyright (c) Microsoft Corporation. All rights reserved. 
    rem    This code is licensed under the Microsoft Public License. 
    rem    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF 
    rem    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY 
    rem    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR 
    rem    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT. 
    rem 
    rem **************************************************************************** 
    rem 
    rem CMD script to add a user to the SQL Server sysadmin role 
    rem 
    rem Input:  %1 specifies the instance name to be modified. Defaults to SQLEXPRESS. 
    rem         %2 specifies the principal identity to be added (in the form "<domain>\<user>"). 
    rem            If omitted, the script will request elevation and add the current user (pre-elevation) to the sysadmin role. 
    rem            If provided explicitly, the script is assumed to be running elevated already. 
    rem 
    rem Method: 1) restart the SQL service with the '-m' option, which allows a single connection from a box admin 
    rem            (the box admin is temporarily added to the sysadmin role with this start option) 
    rem         2) connect to the SQL instance and add the user to the sysadmin role 
    rem         3) restart the SQL service for normal connections 
    rem 
    rem Output: Messages indicating success/failure. 
    rem         Note that if elevation is done by this script, a new command process window is created: the output of this 
    rem         window is not directly accessible to the caller. 
    rem 
    rem 
    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) 
    rem 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 
    rem 
    rem elevate if <domain/user> was defaulted 
    rem 
    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 
    rem 
    rem determine if the SQL service is running 
    rem 
    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 
    rem 
    rem if required, stop the SQL service 
    rem 
    if .%srvstate% == .1 goto startm 
    set srvstarted=1 
    @echo Stop the '%sqlservice%' service ... 
    net stop %sqlservice% 
    if errorlevel 1 goto stoperror 
    :startm 
    rem 
    rem start the SQL service with the '-m' option (single admin connection) and wait until its STATE is '4' (STARTED) 
    rem also use trace flags as follows: 
    rem     3659 - log all errors to errorlog 
    rem     4010 - enable shared memory only (lpc:) 
    rem     4022 - do not start autoprocs 
    rem 
    @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 
    rem 
    rem add the specified user to the sysadmin role 
    rem access tempdb to avoid a misleading shutdown error 
    rem 
    @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 
    rem 
    rem stop the SQL service 
    rem 
    @echo Stop the '%sqlservice%' service ... 
    net stop %sqlservice% 
    if errorlevel 1 goto stoperror 
    if .%srvstarted% == .0 goto exit 
    rem 
    rem start the SQL service for normal connections 
    rem 
    net start %sqlservice% 
    if errorlevel 1 goto starterror 
    goto exit 
    rem 
    rem handle unexpected errors 
    rem 
    :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
2
对我有用。 有一件事要记住，确保将.js扩展名映射到“Microsoft® Windows Based Script Host”，否则这种巫术不起作用（我的映射到了notepad.exe）。 - Phil Cooper
2
链接已失效 - “存档画廊已被停用”。 - stuartd
3
好的，请发送需要翻译的具体内容，我会尽力满足您的需求。 - Chris Gessler
如果您使用上述脚本添加自己作为管理员时遇到问题，请尝试将您的登录信息添加到数据库中，然后再运行该脚本。 - Ram
2
有人将此添加到GitHub。这个脚本真的可以节省很多时间。https://gist.github.com/wadewegner/1677788 - Elim Garak
请注意，此功能仅适用于英语，不支持其他语言。 - Burnsys
12

微软有一篇文章详细介绍了这个问题的解决方法，步骤非常清晰明了。

https://learn.microsoft.com/zh-cn/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out

简而言之，它涉及到使用 -m 参数启动 sqlserver 实例，就像其他答案建议的那样。不过，微软提供了稍微更为详细的说明。

从开始页面启动 SQL Server Management Studio。在“查看”菜单中，选择“已注册的服务器”。（如果您的服务器尚未注册，请右键单击本地服务器组，指向“任务”，然后单击“注册本地服务器”）。

在“已注册的服务器”区域，右键单击您的服务器，然后单击“SQL Server 配置管理器”。这应该会要求您以管理员身份运行，并打开配置管理器程序。

关闭 Management Studio。

在 SQL Server 配置管理器中，在左侧窗格中选择“SQL Server 服务”。在右侧窗格中找到 SQL Server 的实例。（默认实例的计算机名称后包括 (MSSQLSERVER)。命名实例以大写字母和它们在已注册的服务器中具有的相同名称显示。）右键单击 SQL Server 的实例，然后单击“属性”。

在“启动参数”选项卡上，在“指定启动参数”框中键入 -m，然后单击“添加”。（即破折号再加小写字母 m。）

注意

对于某些较早版本的 SQL Server，没有“启动参数”选项卡。在这种情况下，在“高级”选项卡上，双击“启动参数”。参数将在一个非常小的窗口中打开。请小心不要更改任何现有参数。最后，在末尾添加一个新参数；-m，然后单击“确定”。（即分号再加破折号再加小写字母 m。）

单击“确定”，在提示重启消息后，右键单击服务器名称，然后单击“重新启动”。

SQL Server 重新启动后，您的服务器将处于单用户模式。确保 SQL Server 代理未运行。如果已经启动，则会占用您唯一的连接。

在 Windows 8 开始屏幕上，右键单击 Management Studio 图标。在屏幕底部，选择“以管理员身份运行”。（这将向 SSMS 传递您的管理员凭据。）

注意

对于较早版本的 Windows，以管理员身份运行选项会显示为子菜单。

在某些配置中，SSMS 将尝试建立多个连接。由于 SQL Server 处于单用户模式，因此多个连接将失败。您可以选择执行以下操作之一。进行以下任一操作。

a) 使用 Windows 身份验证（包括您的管理员凭据）使用对象资源管理器连接。展开“安全性”，展开“登录名”，然后双击您自己的登录名。在“服务器角色”页面上，选择“sysadmin”，然后单击“确定”。

b) 与对象资源管理器不同，使用 Windows 身份验证（包括您的管理员

CREATE LOGIN [CONTOSO\PatK] FROM WINDOWS;   ALTER SERVER ROLE
sysadmin ADD MEMBER [CONTOSO\PatK];

c）如果你的SQL Server运行在混合身份验证模式下，请使用Windows身份验证（包括管理员凭据）连接查询窗口。执行以下代码以创建一个新的SQL Server身份验证登录，该登录是sysadmin固定服务器角色的成员。

CREATE LOGIN TempLogin WITH PASSWORD = '************';   ALTER
SERVER ROLE sysadmin ADD MEMBER TempLogin;

警告：

将************替换为强密码。

d）如果您的SQL Server正在运行混合身份验证模式，并且要重置sa帐户的密码，请使用Windows身份验证（包括管理员凭据）连接查询窗口。使用以下语法更改sa帐户的密码。

ALTER LOGIN sa WITH PASSWORD = '************';   Warning

用强密码替换************。

接下来请按照以下步骤将 SQL Server 切换回多用户模式。 关闭 SSMS。

在 SQL Server Configuration Manager 中，在左侧窗格中选择 SQL Server Services。在右侧窗格中，右键单击 SQL Server 实例，然后单击“属性”。

在“启动参数”选项卡上，在“现有参数”框中，选择 -m 然后单击“删除”。

注意

对于某些较早版本的 SQL Server，没有“启动参数”选项卡。在这种情况下，请在“高级”选项卡上双击“启动参数”。参数会在一个非常小的窗口中打开。删除您之前添加的 ;-m，然后单击“确定”。

右键单击服务器名称，然后单击“重启”。

现在您应该能够使用现在成为 sysadmin 固定服务器角色成员之一的帐户之一正常连接。

2

实际上，只需在Sql Server Configuration Manager的启动参数中添加-m，重新启动服务，进入ssms并为您的帐户添加复选框sysadmin，然后删除-m再次重启并像往常一样使用。

数据库引擎服务启动选项

-m以单用户模式启动SQL Server实例。

1
其他答案缺少可视化截图，以下是微软文档的简要总结：

  1. 打开开始菜单，右键单击SQL Server Configuration Manager图标并选择以管理员身份运行。

  2. 右键单击SQL Server实例，然后选择属性。

enter image description here

我们将在此设置SQL服务器以单用户模式启动。
在“启动参数”选项卡中，添加以下内容之一：
a）以单用户模式运行SQL服务器
-m

b) 在单用户模式下运行SQL服务器 + 允许通过SSMS进行无限连接。
-m"Microsoft SQL Server Management Studio - Query"

enter image description here

我强烈推荐 (b)。如果没有它，您甚至无法打开对象资源管理器并打开新查询！因为这算作两个连接，而我们正在单用户模式下运行，不允许这样做。
通过在-m选项后附加应用程序名称，我们允许该应用程序通过无限制的连接。
对于一些早期版本的SQL Server，可能没有启动参数选项卡。在这种情况下，在高级选项卡上双击启动参数。参数会在一个小窗口中打开。请注意不要更改任何现有参数。在最后添加一个新参数;-m，然后选择“确定”。（这是一个分号，然后是一个破折号，然后是小写字母m。）

enter image description here

右键单击您的服务器名称，然后选择重新启动。

enter image description here

SQL Server 重新启动后，您的服务器将处于单用户模式。确保 SQL Server Agent 没有在运行。如果已经开启，则会占用您唯一的连接。另外，请禁用 SQL Reporting Services、分析服务、SSIS 和除 SQL Server 引擎之外的其他所有服务。

enter image description here

从Windows开始菜单中，右键单击“管理工具”，然后选择“以管理员身份运行”。这将向SSMS传递您的管理员凭据。 连接对象资源管理器。展开“安全性”、“登录名”，右键单击自己的登录名，在“服务器角色”页面上选择sysadmin。

enter image description here

enter image description here

英译中：
  1. 关闭管理工具。

下面几个步骤将把 SQL Server 改回多用户模式。我们需要撤销第 3 至第 5 步。

  1. 在 SQL Server 配置管理器中，右侧窗格中右键单击 SQL Server 实例，然后选择“属性”。

  2. 在“启动参数”选项卡中，在“现有参数”框中选择“-m”，然后选择“删除”。

  3. 重新启动 SQL Server 服务，并根据需要重新启用 SQL Server 代理、报表服务等。

enter image description here

参考资料：

微软说明

mssqltips - 图形教程

1

我有意从MS SQL Server 2019中删除了我的系统管理员权限，因为我正在测试一个错误。我能够在单用户模式下重新获得权限，并使用SSMS中的管理员身份登录，而无需重新安装。我是这台计算机的管理员，否则这是不可能的。

  1. 打开SQL Server 2019配置管理器并停止您的SQL实例服务 (节点 SQL Server Services -> SQL Server (yourinstancename))
  2. 在属性（节点 SQL Server Services -> SQL Server （yourinstancename） -> 属性 -> 启动参数）中输入 -m，单击添加和应用按钮（完成后应该只有包含这两个符号 -m 的一行现有参数）
  3. 点击确定。
  4. 启动先前停止的服务。
  5. 以管理员身份启动SSMS。
  6. 将sysadmin权限添加到我的用户（安全性 -> 登录 -> 新登录 -> 服务器角色）。
  7. 删除之前添加的额外的-m启动参数。
  8. 重启SQL Server服务。

Reference:
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-ver16

0

如果您想添加一个域用户，这个脚本会为您节省一些时间。

--
-- 1) CREATE LOGIN domain\USERNAME
--
DECLARE @domain VARCHAR(MAX);  
DECLARE @template VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)

SET @domain = DEFAULT_DOMAIN();
SET @template = 'CREATE LOGIN "{domain}\USERNAME" FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'
SET @sql = REPLACE(@template, '{domain}', @domain)

EXECUTE (@sql)

--
-- 2) GRANT SYSADMIN server level permission on domain\USERNAME 
--
DECLARE @domain VARCHAR(MAX);  
DECLARE @template VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)

SET @domain = DEFAULT_DOMAIN();
SET @template = 'EXEC sp_addsrvrolemember @loginame = N''{domain}\USERNAME '', @rolename = N''sysadmin'''
SET @sql = REPLACE(@template, '{domain}', @domain)

EXECUTE (@sql)

只需将“USERNAME”替换为您自己的用户名即可。您不必输入域名，因为它将使用DEFAULT_DOMAIN()动态确定。有趣的事实是：您还可以使用此功能快速添加相同的用户，覆盖位于不同域上的多个数据库。使用SSMS的“在多个数据库上运行查询”功能运行此命令。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接