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
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).
- Linked-server is created using a SQL login/pwd, say
readonly, which, as the name implies, has READ permissions on the target server & DB.
- Testing the query when
MULTI_USERmode seems "faster" than when it runs &
SINGLE_USERmode, but I don't have any concrete metric of that, just the observation/feels.
- 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
MyDBlives) is 2008R2.