1

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.

0

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.

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.