I'm currently using SQL Server 2005 which has a number of databases on for individiaul clients.
I need to create a single read only login for each of my clients, however I cannot allow them to see the other databases in SSMS as they contain other clients company names.
I've run the following which gives me exactly what I need, however this gives them db_owner rather than read only. When removing db_owner they can no longer see the DB in SSMS.
--Step 1: (create a new user) create LOGIN hello WITH PASSWORD='foo', CHECK_POLICY = OFF; -- Step 2:(deny view to any database) USE master; GO DENY VIEW ANY DATABASE TO hello; -- step 3 (then authorized the user for that specific database , you have to use the master by doing use master as below) USE master; GO ALTER AUTHORIZATION ON DATABASE::yourDB TO hello; GO
Is this possible? I can upgrade SQL if required if it's not possible in 2005.