0

I've been researching and can't find a definitive answer. So I apologize if this question is more obvious than I suspect. All documentation I can find definitively says 'sysadmin' roles can do absolutely anything on the server including accessing all databases.

Yet, if I try to connect using an explicitly defined database in the connection string (Using windows authentication), the sysadmin user fails to login if they have not been mapped to that database as well.

What is the definitive answer here? Is there a resource you can provide that specifies this?

Improve this question
1
  • sysadmin is like god, it could do everything ;)
    – Dominique Boucher
    Sep 23 '20 at 14:29
3

A sysadmin can use any database and also specify any database in the connection string. The special dbo user is used whenever a sysadmin does anything inside a database.

If you failed to connect to a specific database, then I'd say that the database doesn't exist (or is inaccessible) or you aren't a sysadmin. :-)

(Sorry I don't have something to point to the specifics about having a database in the connection string.)

Improve this answer
3
  • Ok. Thank you for your answer. Based on your feedback, I dug further. I found that my domain admin account could login to a specific database. What I found is that the domain user I was using to login was in the "Domain Guests" group. Once I moved the user to "Domain Users" it was able to connect to the server. Any ideas? I don't know why a user specified as a sysadmin in SQL would have a problem logging in to a specific database if it is only a "Domain Guest." This same user can connect to the server in general and access all the databases (such as through SSMS).
    – Appleoddity
    Sep 23 '20 at 14:11
  • Well, I feel like a goofball. But, I realized that the user was NOT a sysadmin, but instead a serveradmin. It didn't have anything to do with domain guest, or not. I forgot that I had temporarily mapped the user on one of the database servers. Once in the sysadmin role, it could login as expected to the database.
    – Appleoddity
    Sep 23 '20 at 15:12
  • Glad you figure it out! :-)
    – Tibor Karaszi
    Sep 23 '20 at 15:36

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.