LfbFmraW

1@echo off
2rem
3rem ****************************************************************************
4rem
5rem    Copyright (c) Microsoft Corporation. All rights reserved.
6rem    This code is licensed under the Microsoft Public License.
7rem    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
8rem    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
9rem    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
10rem    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
11rem
12rem ****************************************************************************
13rem
14rem CMD script to add a user to the SQL Server sysadmin role
15rem
16rem Input:  %1 specifies the instance name to be modified. Defaults to SQLEXPRESS.
17rem         %2 specifies the principal identity to be added (in the form "<domain>\<user>").
18rem            If omitted, the script will request elevation and add the current user (pre-elevation) to the sysadmin role.
19rem            If provided explicitly, the script is assumed to be running elevated already.
20rem
21rem Method: 1) restart the SQL service with the '-m' option, which allows a single connection from a box admin
22rem            (the box admin is temporarily added to the sysadmin role with this start option)
23rem         2) connect to the SQL instance and add the user to the sysadmin role
24rem         3) restart the SQL service for normal connections
25rem
26rem Output: Messages indicating success/failure.
27rem         Note that if elevation is done by this script, a new command process window is created: the output of this
28rem         window is not directly accessible to the caller.
29rem
30rem
31setlocal
32set sqlresult=N/A
33if .%1 == . (set sqlinstance=SQLEXPRESS) else (set sqlinstance=%1)
34if /I %sqlinstance% == MSSQLSERVER (set sqlservice=MSSQLSERVER) else (set sqlservice=MSSQL$%sqlinstance%)
35if .%2 == . (set sqllogin="%USERDOMAIN%\%USERNAME%") else (set sqllogin=%2)
36rem remove enclosing quotes
37for %%i in (%sqllogin%) do set sqllogin=%%~i
38@echo Adding '%sqllogin%' to the 'sysadmin' role on SQL Server instance '%sqlinstance%'.
39@echo Verify the '%sqlservice%' service exists ...
40set srvstate=0
41for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
42if .%srvstate% == .0 goto existerror
43rem
44rem elevate if <domain/user> was defaulted
45rem
46if NOT .%2 == . goto continue
47echo 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"
48call "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js" "%cd%" %0 "%sqllogin%"
49del "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js"
50goto :EOF
51:continue
52rem
53rem determine if the SQL service is running
54rem
55set srvstarted=0
56set srvstate=0
57for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
58if .%srvstate% == .0 goto queryerror
59rem
60rem if required, stop the SQL service
61rem
62if .%srvstate% == .1 goto startm
63set srvstarted=1
64@echo Stop the '%sqlservice%' service ...
65net stop %sqlservice%
66if errorlevel 1 goto stoperror
67:startm
68rem
69rem start the SQL service with the '-m' option (single admin connection) and wait until its STATE is '4' (STARTED)
70rem also use trace flags as follows:
71rem     3659 - log all errors to errorlog
72rem     4010 - enable shared memory only (lpc:)
73rem     4022 - do not start autoprocs
74rem
75@echo Start the '%sqlservice%' service in maintenance mode ...
76sc start %sqlservice% -m -T3659 -T4010 -T4022 >nul
77if errorlevel 1 goto startmerror
78:checkstate1
79set srvstate=0
80for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
81if .%srvstate% == .0 goto queryerror
82if .%srvstate% == .1 goto startmerror
83if NOT .%srvstate% == .4 goto checkstate1
84rem
85rem add the specified user to the sysadmin role
86rem access tempdb to avoid a misleading shutdown error
87rem
88@echo Add '%sqllogin%' to the 'sysadmin' role ...
89for /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
90rem
91rem stop the SQL service
92rem
93@echo Stop the '%sqlservice%' service ...
94net stop %sqlservice%
95if errorlevel 1 goto stoperror
96if .%srvstarted% == .0 goto exit
97rem
98rem start the SQL service for normal connections
99rem
100net start %sqlservice%
101if errorlevel 1 goto starterror
102goto exit
103rem
104rem handle unexpected errors
105rem
106:existerror
107sc query %sqlservice%
108@echo '%sqlservice%' service is invalid
109goto exit
110:queryerror
111@echo 'sc query %sqlservice%' failed
112goto exit
113:stoperror
114@echo 'net stop %sqlservice%' failed
115goto exit
116:startmerror
117@echo 'sc start %sqlservice% -m' failed
118goto exit
119:starterror
120@echo 'net start %sqlservice%' failed
121goto exit
122:exit
123if .%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%.)
124endlocal