fypDanmM

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 /P sqlinstance=Enter SQL instance name, or default to SQLEXPRESS: ) else (set sqlinstance=%1)
34if .%sqlinstance% == . (set sqlinstance=SQLEXPRESS)
35if /I %sqlinstance% == MSSQLSERVER (set sqlservice=MSSQLSERVER) else (set sqlservice=MSSQL$%sqlinstance%)
36if .%2 == . (set sqllogin="%USERDOMAIN%\%USERNAME%") else (set sqllogin=%2)
37rem remove enclosing quotes
38for %%i in (%sqllogin%) do set sqllogin=%%~i
39@echo Adding '%sqllogin%' to the 'sysadmin' role on SQL Server instance '%sqlinstance%'.
40@echo Verify the '%sqlservice%' service exists ...
41set srvstate=0
42for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
43if .%srvstate% == .0 goto existerror
44rem
45rem elevate if <domain/user> was defaulted
46rem
47if NOT .%2 == . goto continue
48echo 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"
49call "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js" "%cd%" %0 "%sqllogin%"
50del "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js"
51goto :EOF
52:continue
53rem
54rem determine if the SQL service is running
55rem
56set srvstarted=0
57set srvstate=0
58for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
59if .%srvstate% == .0 goto queryerror
60rem
61rem if required, stop the SQL service
62rem
63if .%srvstate% == .1 goto startm
64set srvstarted=1
65@echo Stop the '%sqlservice%' service ...
66net stop %sqlservice%
67if errorlevel 1 goto stoperror
68:startm
69rem
70rem start the SQL service with the '-m' option (single admin connection) and wait until its STATE is '4' (STARTED)
71rem also use trace flags as follows:
72rem     3659 - log all errors to errorlog
73rem     4010 - enable shared memory only (lpc:)
74rem     4022 - do not start autoprocs
75rem
76@echo Start the '%sqlservice%' service in maintenance mode ...
77sc start %sqlservice% -m -T3659 -T4010 -T4022 >nul
78if errorlevel 1 goto startmerror
79:checkstate1
80set srvstate=0
81for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
82if .%srvstate% == .0 goto queryerror
83if .%srvstate% == .1 goto startmerror
84if NOT .%srvstate% == .4 goto checkstate1
85rem
86rem add the specified user to the sysadmin role
87rem access tempdb to avoid a misleading shutdown error
88rem
89@echo Add '%sqllogin%' to the 'sysadmin' role ...
90for /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
91rem
92rem stop the SQL service
93rem
94@echo Stop the '%sqlservice%' service ...
95net stop %sqlservice%
96if errorlevel 1 goto stoperror
97if .%srvstarted% == .0 goto exit
98rem
99rem start the SQL service for normal connections
100rem
101net start %sqlservice%
102if errorlevel 1 goto starterror
103goto exit
104rem
105rem handle unexpected errors
106rem
107:existerror
108sc query %sqlservice%
109@echo '%sqlservice%' service is invalid
110goto exit
111:queryerror
112@echo 'sc query %sqlservice%' failed
113goto exit
114:stoperror
115@echo 'net stop %sqlservice%' failed
116goto exit
117:startmerror
118@echo 'sc start %sqlservice% -m' failed
119goto exit
120:starterror
121@echo 'net start %sqlservice%' failed
122goto exit
123:exit
124if .%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%.)
125endlocal
126pause