0

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?

  • Is the User in the linked server the same as the user that has the db in single user mode? – WEI_DBA Oct 31 '16 at 15:05
  • Being in single user mode will not implicitly prevent you from connecting. If it is the only connection to the database when you would still be able to access it. Have you tried maintaining a connection with SSMS and then attempting to access data via the linked server? – Nic Oct 31 '16 at 15:06
  • @WEI_DBA no, I'm logged in to Bar2008R2 & connected to MyDB as myself, a Windows login with admin rights. – NateJ Oct 31 '16 at 15:06
  • @Nic It's possible that nobody else was connected to MyDB at the time, not even me. I may have to test that theory. – NateJ Oct 31 '16 at 15:07
  • Have you considered using restricted user instead of single user? – Aaron Bertrand Oct 31 '16 at 15:15
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.