I am adding a new Login with SQL Server Authentication. I set its Server Role as public and then went into User Mapping, selecting the only database this user should have access to. I then change the Default Schema to dbo and made this user the db_owner.
I then connect to the instance using the new user's credentials and I can see not only the database he should have access to but all the other attached databases.
How can I limit this user to just see the database he has access to?
Thanks in advance!
Best Answer
To do that you would need to remove the guest account for all the other databases. While the user can see the other databases, he can't open them and do anything (unless the guest account or the public role within those databases have access to anything).