0

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?

  • Is tom a Windows Authenticated SQL Server Login (Windows Domain Account registered with the SQL Server instance) or just a simple SQL Server Login? When the user tom logs in to the SQL Server, is he using Windows Authentication or (a/the) SQL Server Login tom? Please edit your question and add as many details as possible. – John aka hot2use Jun 30 at 8:12
1

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_LOGIN

sp_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_who to see if tom is actually logged on to the server you think he is.

| improve this answer | |
0

Like mentionned by James, if Tom didn't see the database with SA privilege, then it means the issue is somewhere else...

I'm having the feeling that Tom is not connecting on the same instance you are.

Run :

Select @@servername "server", SERVERPROPERTY('instancename') "Instance";

from both machine and check if you get the same result.

| improve this answer | |

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.