Пакетный скрипт SQL Express: добавление учетной записи в роль SQL Server SysAdmin
Попробуйте этот сценарий и посмотрите, добавляет ли он учетную запись пользователя, которую вы выполняете, в качестве доступа sysadmin к экземпляру SQL Server на вашем компьютере с Windows. Это нужно будет сохранить в файл .cmd или .bat путем переименования текстового документа, а затем выполнить его как пакетный скрипт.
Я получил это из GitHub "Скрипт для добавления текущего пользователя к роли sysadmin в SQL Server" несколько лет назад и использовал его для установки SQL Express, чтобы предоставить sysadmin доступ к локальной учетной записи администратора на сервере. Я сохранил его после того, как подтвердил, что это сработало, поэтому я поделился этим с другими здесь.
@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
