Sql-server – Linked-server query against remote DB does NOT fail when DB is in SINGLE_USER mode

linked-serversql serversql-server-2005sql-server-2008-r2

This is really strange to me. I have a primary server Foo2005 and it has a linked-server entry pointing to Bar2008R2; say the name of the linked-server is BarLINK. I write a query such as SELECT * FROM BarLINK.MyDB.dbo.Stuff. The catch is, over on Bar, MyDB is in SINGLE_USER mode. Why would this query still succeed? I would expect to get the error like "Could not open the specified database since it's in SINGLE_USER mode and is already open by a user" (forgive me for not remembering the exact phraseology).

Notes:

  1. Linked-server is created using a SQL login/pwd, say readonly, which, as the name implies, has READ permissions on the target server & DB.
  2. Testing the query when MyDB is in MULTI_USER mode seems "faster" than when it runs & MyDB is in SINGLE_USER mode, but I don't have any concrete metric of that, just the observation/feels.
  3. As the names indicate, server Foo2005 (the "local" server where the query lives) is SQL 2005, while server Bar2008R2 (the "remote" server where the query is being sent / where MyDB lives) is 2008R2.

Any ideas?

Best Answer

Upon re-trying and attempting to reproduce this problem, it seems I cannot. I think Nic said it best so far: "being in single-user mode doesn't implicitly prevent connections". At the time, the linked-server query must have been the first and only thing connecting to that DB.

Thanks also to Aaron Bertrand for suggesting the restricted_user mode for future cases.

Related Question