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?

Improve this question
10
  • Is the User in the linked server the same as the user that has the db in single user mode?
    – SS_DBA
    Commented Oct 31, 2016 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
    Commented Oct 31, 2016 at 15:06
  • @WEI_DBA no, I'm logged in to Bar2008R2 & connected to MyDB as myself, a Windows login with admin rights.
    – NateJ
    Commented Oct 31, 2016 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
    Commented Oct 31, 2016 at 15:07
  • Have you considered using restricted user instead of single user?
    – Aaron Bertrand
    Commented Oct 31, 2016 at 15:15

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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