SQL Server Read-Only Mode

I was migrating data from one system to another today.  The data is currently stored in a SQL Server database.  

This is the final data migration prior to the go-live date of the new system.  I wanted to make sure that the users did not accidentally change any data in the old system after this final migration.  Therefore, the first step was to set the current database to read-only.

USE master
GO

ALTER DATABASE [RAR] SET READ_ONLY WITH NO_WAIT
GO

I tried executing the above statement but it failed with the following message:

Database state cannot be changed while other users are using the database 'RAR'
ALTER DATABASE statement failed.

Even though today was a non-business day, someone (or some process) was using the database.  To identify that person, I used SQL Server's sys.sysprocesses system view to identify users within the database (adapted from this Stack Overflow answer):

SELECT 
    DB_NAME(dbid) as DBName, 
    loginame as LoginName,
    spid,
    hostname,
    program_name
FROM
    sys.sysprocesses
WHERE 
    dbid > 0 
and DB_NAME(dbid) = 'RAR'

And here are the results.  I blurred out the particulars for security reasons, but all four LoginNames are the same.  And they all belong to me.  It looks like I'm the reason this won't work!  I ran the above query from the computer listed in line 2.  The other three entries are all coming from a different development computer.  

I connected to the other device and closed SSMS and Microsoft Access (the two programs that had active connections to the "RAR" database).  I then closed the SSMS tab where I had run the above query because that was also occupying an active connection.  After that, I re-ran the following query.  It completed successfully this time.

USE master
GO

ALTER DATABASE [RAR] SET READ_ONLY WITH NO_WAIT
GO

To confirm this, I right-clicked on the database name in SSMS and chose "Properties."  I then clicked on the Options page and scrolled down to the bottom where "State: Database Read-Only" was set to True:

Image by Daniel Kirsch from Pixabay