Sql-server – SQL/SSMS read only user who can only view a single database

sql serversql-server-2005ssms

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.

Best Answer

Would it work for you to create a user for the login and assign them a read-only role?

<!-- language: lang-sql -->
CREATE USER hello FOR LOGIN hello
GO
EXEC sp_addrolemember 'db_datareader', 'hello'

They shouldn't be a member of the db_owner role after this, unless it's set somewhere by default to add any new users to the db_owner role. This should limit to SELECT against the user's database only. If you want to limit views to specific tables, you'll have to create a custom role to limit that access.

Related Topic