Skip to content

Commit fe63933

Browse files
PiJoCoderPiJoCoder
committed
rewrote commands using Powershell
1 parent 4c39df8 commit fe63933

1 file changed

Lines changed: 26 additions & 27 deletions

File tree

‎docs/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out.md‎

Lines changed: 26 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -62,63 +62,62 @@ The following table summarizes the different ways to start your instance in sing
6262
For step-by-step instructions about how to start [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] in single-user mode, see [Start SQL Server in Single-User Mode](../../database-engine/configure-windows/start-sql-server-in-single-user-mode.md).
6363

6464

65-
### Using Command Prompt
65+
### Using Powershell
6666

67-
1. Open a Command Prompt as Administrator
68-
1. Stop SQL Server service so it can be restarted with single-user mode, using the following command:
67+
1. Open a Windows Powershell command - Run as an Administrator
68+
1. Set up service name and SQL Server instance, and Windows login variables. Replace these with values to match your environment
6969

70-
```Command Prompt
71-
net stop MSSQL$instancename
70+
```powershell
71+
$service_name = "MSSQL`$instancename" # for a default instace use: "MSSQLSERVER"
72+
$sql_server_instance = "machine_name\instance"
73+
$login_to_be_granted_access = "[CONTOSO\PatK]"
7274
```
7375

74-
For a default instance name use: `net stop MSSQLSERVER`
76+
1. Stop SQL Server service so it can be restarted with single-user mode, using the following command:
7577

78+
```powershell
79+
net stop $service_name
80+
```
7681

7782
1. Now start your SQL Server instance in a single user mode and only allow SQLCMD.exe to connect (/mSQLCMD)
7883

7984
> [!NOTE]
8085
> Be sure to use upper-case SQLCMD
8186
82-
```Command Prompt
83-
net start MSSQL$instance /mSQLCMD
87+
```powershell
88+
net start $service_name /mSQLCMD
8489
```
8590

8691
1. Using **SQLCMD** execute a CREATE LOGIN command followed by ALTER SERVER ROLE command. This step assumes you have logged into Windows with an account that is a member of the Local Administrators group. Replace the domain and login name with the credentials you want to give Sysadmin access.
8792

88-
```Command Prompt
89-
sqlcmd.exe -E -S.\instancename -Q "CREATE LOGIN [CONTOSO\PatK] FROM WINDOWS; ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\PatK]; "
93+
```powershell
94+
sqlcmd.exe -E -S $sql_server_instance -Q "CREATE LOGIN $login_to_be_granted_access FROM WINDOWS; ALTER SERVER ROLE sysadmin ADD MEMBER $login_to_be_granted_access; "
9095
```
9196

9297
> [!NOTE]
93-
> If you receive the following error, you need to ensure no other SQLCMD has connected to SQL Server: </br>
98+
> If you receive the following error, you must ensure no other SQLCMD has connected to SQL Server: </br>
9499
> `Sqlcmd: Error: Microsoft ODBC Driver X for SQL Server : Login failed for user 'CONTOSO\BobD'. Reason: Server is in single user mode. Only one administrator can connect at this time..`
95100
96-
97101
1. **Mixed Mode (optional):** If your SQL Server is running in mixed authentication mode, you can also:
98-
1. Grant a SQL login Sysadmin role membership. Execute code such as the following to create a new SQL Server authentication login that is a member of the sysadmin fixed server role. Replace '************' with a strong password.
102+
1. Grant a SQL login Sysadmin role membership. Execute code such as the following to create a new SQL Server authentication login that is a member of the sysadmin fixed server role. Replace "?j8:z$G=JE9" with a strong password of your choice.
99103

100-
```Command Prompt
101-
sqlcmd.exe -E -S.\instancename -Q "CREATE LOGIN TempLogin WITH PASSWORD = '************'; ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin; "
104+
```powershell
105+
$strong_password = "j8:zG=J?E9"
106+
sqlcmd.exe -E -S $sql_server_instance -Q "CREATE LOGIN TempLogin WITH PASSWORD = '$strong_password'; ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin; "
102107
```
103108
104-
1. Also, if your SQL Server is running in mixed authentication mode and you want to reset the password of the **sa** account. Change the password of the sa account with the following syntax. Be sure to replace '************' with a strong password:
109+
1. Also, if your SQL Server is running in mixed authentication mode and you want to reset the password of the enabled **sa** account. Change the password of the sa account with the following syntax. Be sure to replace "j8:zG=J?E9" with a strong password of your choice:
105110
106-
```Command Prompt
107-
sqlcmd.exe -E -S.\instancename -Q "ALTER LOGIN sa WITH PASSWORD = '************'; "
111+
```powershell
112+
$strong_password = "j8:zG=J?E9"
113+
sqlcmd.exe -E -S $sql_server_instance -Q "ALTER LOGIN sa WITH PASSWORD = $strong_password; "
108114
```
109115
110116
1. Stop and restart your SQL Server instance in multi-user mode
111117
112-
```Command Prompt
113-
net stop MSSQL$instancename & net start MSSQL$instancename
118+
```powershell
119+
net stop $service_name & net start $service_name
114120
```
115-
116-
1. Verify that you can connect to SQL Server using the account for which you granted access and if that account is member of Sysadmin role.
117-
118-
```Command Prompt
119-
runas /noprofile /user:CONTOSO\PatK "sqlcmd.exe -E -S.\instancename -Q \"SELECT IS_SRVROLEMEMBER( 'sysadmin')\""
120-
```
121-
122121

123122
### Using SQL Server Configuration Manager and Management Studio (SSMS)
124123

0 commit comments

Comments
 (0)