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

See also