I am creating several logins for different databases that sit on the same server.
Each user will have access to 1 database and none of the others
After creating 1 login, I tested it to see if the denyreader works - However, I can still physically see the other databases themselves - I can't access any of the databases - is there a way of hiding the databases completely that I shouldn't have access to?
No. I just tried with SQL2000, SQL2005, and SQL2008. The newly created login can only "see" the databases that they have access to when they use the Microsoft tools.
Must be doing something wrong then....
I followed these steps:
1) Login to Server with windows authentication (Using MSSM Studio 2008)
2) In Security / Logins Right Click and Select New Login
3) Create SSA Login
4) Change Default DB to required DB
5) In User Mapping Select Required DB and default Schema to dbo
6) Close down SQL Server and login as new user
I can still see all of the other databases on the server