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

Mohamed Elqassas Mvp
Nov 28, 2017
1.2k
0
2
Blog

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

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

declare @IsSingleUser as sql_variant
set @IsSingleUser = (select SERVERPROPERTY('IsSingleUser'))
select @IsSingleUser as IsSingleUserID,
case @IsSingleUser
when 0 then 'Multiple User'
when 1 then 'Single user'
else 'Invalid Input'
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 *******