SQL Server - Check If The Server Is In Single-User Mode

SQL Server: Check if the Server is in single-user mode using ServerProperty

In this blog, we will learn how to check if the Server is in a single-user mode using "SERVERPROPERTY".

What's the "SERVERPROPERTY"?

"SERVERPROPERTY" is a System-defined function used to return the SQL Server Instance Information.

"SERVERPROPERTY" Syntax

1. SERVERPROPERTY ('propertyname')

Check if the Server is in a single-user mode using T-SQL

Use ISSINGLEUSER property to check if the Server is in a single-user mode - Where (0=Multiple User) & (1=Single User)

Example

1. declare @IsSingleUser as sql_variant
2. set @IsSingleUser = (select SERVERPROPERTY('IsSingleUser'))
3. select @IsSingleUser as IsSingleUserID,
4. case @IsSingleUser
5. when 0 then 'Multiple User'
6. when 1 then 'Single user'
7. else 'Invalid Input'
8. end as 'IsSingleUser'

Output



Check if the Server is in a single-user mode using PowerShell

You can use Windows PowerShell to invoke SQL command on a reachable server within the network using Invoke-Sqlcmd cmdlet, as the following.

• Open Windows PowerShell as Administrator
  
• Type the Invoke-Sqlcmd with the below parameters.
  
  
• -query: the SQL query that you need to run on the remote server.
• -ServerInstance: the SQL server instance name.
• -Username: the username that has sufficient permission to access and execute SQL query on the remote server.
• -Password: the password of the elevated user.
  
  PS SQLSERVER:\> Invoke-Sqlcmd -query "select SERVERPROPERTY('ISSINGLEUSER') as 'Is Single User'" -ServerInstance "epm\epmdb" -Username sa -Password *******

Applies to

• SQL Server 2008.
• SQL Server 2012.
• SQL Server 2014.
• SQL Server 2016.
• SQL Server 2017.

Reference

• How to get the SQL Server Instance Information using “SERVERPROPERTY”.

See also

Download the "Get The Detailed SQL Server Information" Script.