0

Our business has just changed Active Directories and the domain changed, from "YMS" to "YMSNET". So I used to be able to log in with "YMS\tkol" and I can now log in with "YMSNET\tkol" (these usernames and domains are faked for the purpose of example), but when I log in as that now, I can't actually expand any of the databases or look at any of the tables, I can just see a list of the database names. When I try to expand a database in the UI it says "This database is not accessible (Object Explorer)."

Now I have another user, called "sqluser", and I keep trying to use that user to log in as well by changing the Authentication Method to SQL Server Authentication rather than Windows Authentication. But I get Microsoft SQL Server, Error: 4064

Now I know this sqluser user exists and the password is correct, because I can authenticate to the server and successfully interact with the tables from an external process on a separate computer on the same network (node.js, package mssql). And I used the query on the accepted answer on this question, and found my sqluser is there, with roles db_accessadmin, db_ddladmin, db_owner. And yet it still won't let me log in with that user in the SQL Server Management Studio UI

How can I get this working again and log in with my sqluser account? Or add the appropriate permissions for my YMSNET\tkol account?

--- edit ---

My first idea is that, because I can log into the UI with YMSNET\tkol, but I can interact with the databases externally with sqluser, that there is some query or command I can run with sqluser that will add permissions for YMSNET\tkol so that that user can now look at all the databases and tables. I don't know which commands I'd run for that.

TKoL
  • 13,158
  • 3
  • 39
  • 73

1 Answers1

3

It can be because your account's default database is mapped to some another db which is not available for you, for instance, you have no permissions there, or that database not exists anymore etc.

Your organization DBA can fix it by:

ALTER LOGIN [sqluser] WITH DEFAULT_DATABASE = [rightDB]

Default db name can be checked by:

select default_database_name from sys.server_principals
where name = 'sqluser'

This property can be overridden by opening "Options" of SSMS connection window and specifying it explicitly:

enter image description here

Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
  • Any idea how to find out what the `rightDB` is? I unfortunately don't have an organization DBA, I'm the closest thing to that. – TKoL Mar 06 '19 at 12:28
  • OK so I've discovered that the current default database is called MyDB, which when I log in to SQL Server Management Studio, I can't actually see a database called that so you seem to be right that maybe the database doesn't exist anymore. So which database should I switch it to? There are many options, including 'master'... – TKoL Mar 06 '19 at 12:33
  • 1
    You can switch to **master**, it is pretty safe as for now. Later, you can change it to another one. – Alexander Volok Mar 06 '19 at 12:35
  • That's great, I will try that and report back. Thank you – TKoL Mar 06 '19 at 12:35
  • Yes, this was perfect! I noticed that every other person on the system has a default db of master, so I changed it to master using your command and now I can access it all. Thank you so much! – TKoL Mar 06 '19 at 12:37
  • 1
    Great, nice that it was fixed – Alexander Volok Mar 06 '19 at 12:38