如何授予自己对本地 SQL Server 实例的管理员访问权限？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9889334/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I grant myself admin access to a local SQL Server instance?
提问by SkonJeet
I installed SQL Server 2008 R2 to my local machine. But, I can't create a new database because of rights (or lack of).
我在本地机器上安装了 SQL Server 2008 R2。但是，由于权限（或缺乏），我无法创建新数据库。
"CREATE DATABASE PERMISSION DENIED"
“创建数据库权限被拒绝”
So, I tried to assign the admin privileges to my current login
因此，我尝试将管理员权限分配给我当前的登录名
"User does not have permission to perform this action."
“用户无权执行此操作。”
I also tried to create a new login that would have admin privileges but with no luck. How do I grant myself admin rights so that I can create a database?I can re-install, but I prefer not to.
我还尝试创建一个具有管理员权限但没有运气的新登录名。如何授予自己管理员权限以便我可以创建数据库？我可以重新安装，但我不想这样做。
采纳答案by Brian Knight
Yes - it appears you forgot to add yourself to the sysadmin role when installing SQL Server. If you are a local administrator on your machine, this blog postcan help you use SQLCMD to get your account into the SQL Server sysadmin group without having to reinstall. It's a bit of a security hole in SQL Server, if you ask me, but it'll help you out in this case.
是的 - 安装 SQL Server 时，您似乎忘记将自己添加到 sysadmin 角色。如果您是计算机的本地管理员，这篇博文可以帮助您使用 SQLCMD 将您的帐户加入 SQL Server sysadmin 组，而无需重新安装。如果你问我，它是 SQL Server 中的一个安全漏洞，但在这种情况下它会帮助你。
回答by Darren
Open a command prompt window. If you have a default instance of SQL Server already running, run the following command on the command prompt to stop the SQL Server service:
打开命令提示符窗口。如果您已经运行了 SQL Server 的默认实例，请在命令提示符下运行以下命令以停止 SQL Server 服务：
net stop mssqlserver
Now go to the directory where SQL server is installed. The directory can for instance be one of these:
现在转到安装 SQL Server 的目录。例如，目录可以是以下之一：
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn
Figure out your MSSQL directory and CDinto it as such:
找出您的 MSSQL 目录并将其CD放入其中：
CD C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
Now run the following command to start SQL Server in single user mode. As
SQLCMDis being specified, only one SQLCMDconnection can be made (from another command prompt window).
现在运行以下命令以单用户模式启动 SQL Server。正如
SQLCMD所指定的，只能建立一个SQLCMD连接（从另一个命令提示符窗口）。
sqlservr -m"SQLCMD"
Now, open another command prompt window as the same user as the one that started SQL Server in single user mode above, and in it, run:
现在，以与上面在单用户模式下启动 SQL Server 的用户相同的用户身份打开另一个命令提示符窗口，并在其中运行：
sqlcmd
And press enter. Now you can execute SQL statements against the SQL Server instance running in single user mode:
然后按回车。现在您可以对在单用户模式下运行的 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
来源。
UPDATEDDo not forget a semicolon after ALTER SERVER ROLE [sysadmin] ADD MEMBER [<<DOMAIN\USERNAME>>];and do not add extra semicolon after GOor the command never executes.
更新后不要忘记分号ALTER SERVER ROLE [sysadmin] ADD MEMBER [<<DOMAIN\USERNAME>>];，也不要在后面添加额外的分号，GO否则命令永远不会执行。
回答by Vince P.
I adopted a SQL 2012 database where I was not a sysadmin but was an administrator on the machine. I used SSMS with "Run as Administrator", added my NT account as a SQL login and set the server role to sysadmin. No problem.
我采用了一个 SQL 2012 数据库，我不是系统管理员，而是机器上的管理员。我将 SSMS 与“以管理员身份运行”一起使用，将我的 NT 帐户添加为 SQL 登录名，并将服务器角色设置为 sysadmin。没问题。
回答by Chris Gessler
Here's a script that claims to be able to fix this.
这是一个声称能够解决此问题的脚本。
Get admin rights to your local SQL Server Express with this simple script
使用这个简单的脚本获取本地 SQL Server Express 的管理员权限
Description
This command script allows you to easily add yourself to the sysadmin role of a local SQL Server instance. You must be a member of the Windows local Administrators group, or have access to the credentials of a user who is. The script supports SQL Server 2005 and later.
The script is most useful if you are a developer trying to use SQL Server 2008 Express that was installed by someone else. In this situation you usually won't have admin rights to the SQL Server 2008 Express instance, since by default only the person installing SQL Server 2008 is granted administrative privileges.
The user who installed SQL Server 2008 Express can use SQL Server Management Studio to grant the necessary privileges to you. But what if SQL Server Management Studio was not installed? Or worse if the installing user is not available anymore?
This script fixes the problem in just a few clicks!
描述
此命令脚本允许您轻松地将自己添加到本地 SQL Server 实例的 sysadmin 角色。您必须是 Windows 本地管理员组的成员，或者有权访问该用户的凭据。该脚本支持 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 呢？或者更糟糕的是，如果安装用户不再可用？
该脚本只需点击几下即可解决问题！
Note: You will need to provide the BAT file with an 'Instance Name' (Probably going to be 'MSSQLSERVER' - but it might not be): you can get the value by first running the following in the "Microsoft SQL Server Management Console":
注意：您需要为 BAT 文件提供一个“实例名称”（可能是“MSSQLSERVER”——但也可能不是）：您可以通过首先在“Microsoft SQL Server 管理控制台”中运行以下命令来获取该值”：
SELECT @@servicename
Then copy the result to use when the BAT file prompts for 'SQL instance name'.
然后将结果复制到 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
回答by Yair Halberstadt
Microsoft has an article about this issue. It goes through it all step by step.
Microsoft 有一篇关于此问题的文章。它一步一步地完成这一切。
In short it involves starting up the instance of sqlserver with -mlike all the other answers suggest. However Microsoft provides slightly more detailed instructions.
简而言之，它涉及-m像所有其他答案所建议的那样启动 sqlserver 实例。但是，Microsoft 提供了更详细的说明。
From the Start page, start SQL Server Management Studio. On the View menu, select Registered Servers. (If your server is not already registered, right-click Local Server Groups, point to Tasks, and then click Register Local Servers.)
In the Registered Servers area, right-click your server, and then click SQL Server Configuration Manager. This should ask for permission to run as administrator, and then open the Configuration Manager program.
Close Management Studio.
In SQL Server Configuration Manager, in the left pane, select SQL Server Services. In the right-pane, find your instance of SQL Server. (The default instance of SQL Server includes (MSSQLSERVER) after the computer name. Named instances appear in upper case with the same name that they have in Registered Servers.) Right-click the instance of SQL Server, and then click Properties.
On the Startup Parameters tab, in the Specify a startup parameter box, type -m and then click Add. (That's a dash then lower case letter m.)
Note
For some earlier versions of SQL Server there is no Startup Parameters tab. In that case, on the Advanced tab, double-click Startup Parameters. The parameters open up in a very small window. Be careful not to change any of the existing parameters. At the very end, add a new parameter ;-m and then click OK. (That's a semi-colon then a dash then lower case letter m.)
Click OK, and after the message to restart, right-click your server name, and then click Restart.
After SQL Server has restarted your server will be in single-user mode. Make sure that that SQL Server Agent is not running. If started, it will take your only connection.
On the Windows 8 start screen, right-click the icon for Management Studio. At the bottom of the screen, select Run as administrator. (This will pass your administrator credentials to SSMS.)
Note
For earlier versions of Windows, the Run as administrator option appears as a sub-menu.
In some configurations, SSMS will attempt to make several connections. Multiple connections will fail because SQL Server is in single-user mode. You can select one of the following actions to perform. Do one of the following.
a) Connect with Object Explorer using Windows Authentication (which includes your Administrator credentials). Expand Security, expand Logins, and double-click your own login. On the Server Roles page, select sysadmin, and then click OK.
b) Instead of connecting with Object Explorer, connect with a Query Window using Windows Authentication (which includes your Administrator credentials). (You can only connect this way if you did not connect with Object Explorer.) Execute code such as the following to add a new Windows Authentication login that is a member of the sysadmin fixed server role. The following example adds a domain user named CONTOSO\PatK.
CREATE LOGIN [CONTOSO\PatK] FROM WINDOWS; ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\PatK];c) If your SQL Server is running in mixed authentication mode, connect with a Query Window using Windows Authentication (which includes your Administrator credentials). Execute code such as the following to create a new SQL Server Authentication login that is a member of the sysadmin fixed server role.
CREATE LOGIN TempLogin WITH PASSWORD = '************'; ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin;Warning:
Replace ************ with a strong password.
d) If your SQL Server is running in mixed authentication mode and you want to reset the password of the sa account, connect with a Query Window using Windows Authentication (which includes your Administrator credentials). Change the password of the sa account with the following syntax.
ALTER LOGIN sa WITH PASSWORD = '************'; WarningReplace ************ with a strong password.
The following steps now change SQL Server back to multi-user mode. Close SSMS.
In SQL Server Configuration Manager, in the left pane, select SQL Server Services. In the right-pane, right-click the instance of SQL Server, and then click Properties.
On the Startup Parameters tab, in the Existing parameters box, select -m and then click Remove.
Note
For some earlier versions of SQL Server there is no Startup Parameters tab. In that case, on the Advanced tab, double-click Startup Parameters. The parameters open up in a very small window. Remove the ;-m which you added earlier, and then click OK.
Right-click your server name, and then click Restart.
Now you should be able to connect normally with one of the accounts which is now a member of the sysadmin fixed server role.
从“开始”页启动 SQL Server Management Studio。在“查看”菜单上，选择“注册的服务器”。（如果您的服务器尚未注册，请右键单击本地服务器组，指向任务，然后单击注册本地服务器。）
在“注册的服务器”区域中，右键单击您的服务器，然后单击“SQL Server 配置管理器”。这应该请求以管理员身份运行的权限，然后打开 Configuration Manager 程序。
关闭管理工作室。
在 SQL Server 配置管理器的左窗格中，选择 SQL Server 服务。在右窗格中，找到您的 SQL Server 实例。（SQL Server 的默认实例在计算机名称后包含 (MSSQLSERVER)。命名实例以大写形式显示，其名称与它们在注册服务器中的名称相同。）右键单击 SQL Server 实例，然后单击“属性”。
在“启动参数”选项卡上的“指定启动参数”框中，键入 -m，然后单击“添加”。（这是一个破折号，然后是小写字母 m。）
笔记
对于某些早期版本的 SQL Server，没有启动参数选项卡。在这种情况下，在“高级”选项卡上，双击“启动参数”。参数在一个非常小的窗口中打开。注意不要更改任何现有参数。最后，添加一个新参数 ;-m 然后单击 OK。（这是一个分号，然后是一个破折号，然后是小写字母 m。）
单击“确定”，在提示重新启动后，右键单击您的服务器名称，然后单击“重新启动”。
SQL Server 重新启动后，您的服务器将处于单用户模式。确保 SQL Server 代理未运行。如果启动，它将占用您唯一的连接。
在 Windows 8 开始屏幕上，右键单击 Management Studio 的图标。在屏幕底部，选择以管理员身份运行。（这会将您的管理员凭据传递给 SSMS。）
笔记
对于早期版本的 Windows，以管理员身份运行选项显示为子菜单。
在某些配置中，SSMS 将尝试建立多个连接。多个连接将失败，因为 SQL Server 处于单用户模式。您可以选择以下操作之一来执行。执行以下操作之一。
a) 使用 Windows 身份验证（包括您的管理员凭据）连接对象资源管理器。展开安全，展开登录，然后双击您自己的登录。在“服务器角色”页上，选择“系统管理员”，然后单击“确定”。
b) 不使用对象资源管理器连接，而是使用 Windows 身份验证（包括您的管理员凭据）连接查询窗口。（如果没有使用对象资源管理器连接，则只能以这种方式连接。）执行如下代码以添加新的 Windows 身份验证登录，该登录是 sysadmin 固定服务器角色的成员。以下示例添加名为 CONTOSO\PatK 的域用户。
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 配置管理器的左窗格中，选择 SQL Server 服务。在右窗格中，右键单击 SQL Server 的实例，然后单击“属性”。
在“启动参数”选项卡上的“现有参数”框中，选择 -m，然后单击“删除”。
笔记
对于某些早期版本的 SQL Server，没有启动参数选项卡。在这种情况下，在“高级”选项卡上，双击“启动参数”。参数在一个非常小的窗口中打开。删除您之前添加的 ;-m，然后单击“确定”。
右键单击您的服务器名称，然后单击重新启动。
现在您应该能够正常连接其中一个帐户，该帐户现在是 sysadmin 固定服务器角色的成员。
回答by norbertas.gaulia
Its actually enough to add -m to startup parameters on Sql Server Configuration Manager, restart service, go to ssms an add checkbox sysadmin on your account, then remove -m restart again and use as usual.
它实际上足以将 -m 添加到 Sql Server 配置管理器的启动参数，重新启动服务，转到 ssms 在您的帐户上添加复选框 sysadmin，然后再次删除 -m restart 并照常使用。
Database Engine Service Startup Options
-m Starts an instance of SQL Server in single-user mode.
-m 在单用户模式下启动 SQL Server 实例。
