I created a user tom(SQL Login) in SQL Server 2019 Standard edition. Then I remove and reinstall the SQL Server 2019 standard edition(same version) and restored all the databases again with the same setting which I did previously with same instance name. Everything is same. Even same machine is used. I copied all the logins through a script provided by Microsoft. I ran the script on new installation/instance and all the logins are back again with same privileges as was granted earlier.
End user tom is unable to view the new database that I created in the List of databases in the SSMS. Tom can view the previously created databases in the list but not the new one. I granted tom sysadmin rights but still he is unable to see the new db I created from his machine. I asked tom to download and reinstall new version of SSMS, which he did but still he is unable to view the new db I created. Interestingly, when i used his credentials to login from my own personal machine to check, that new database will be coming in his(tom) list from my own personal machine, but when he login from his own machine with the same credentials, that new db is not coming into his list of DB. If right/permission is an issue then why it is coming in my own personal machine when I used his credentials to check. So where is an issue? Is it firewall issue in tom machine? Or something else?
Sql-server – New DB is not in the end user list
loginssql server
Related Question
- Sql-server – Deleted sql server windows authentication user
- Sql-server – Login failed for User 18456 State 5. User id works on other machines
- Sql-server – Allow user to create databases on server and to see only the databases that are created by him, without creating login first
- SQL Server SMO – Fix ‘The Login Already Has an Account’ Error in TransferData()
- Sql-server – ny other way apart from sa to login to a migrated instance where the target environment is in a different domain
Best Answer
I don't see how it is possible for tom to have sysadmin rights and not see a database.
Does tom still have sysadmin rights with with the new version of SSMS? Normally refreshing or closing and re-opening SSMS is all that is required
Are you sure he is connecting to the correct server? Sometimes there is a dev or test server with a similar name that the user also has rights on, but is not the same as the one you are working on.
The normal cause of the situation you describe is that 'tom' (assuming 'SQL Server login' as apposed to a windows account) has a different SID in one scenario than in the other. This is corrected by using
SP_CHANGE_USERS_LOGINsp_change_users_login (Transact-SQL)
If you can go to his desk and look over his shoulder OR if he will screen share; you should see where he is doing something different then you are doing when you log on with what you believe is the same account.
Another test is you can use
sp_whoto see if tom is actually logged on to the server you think he is.