Create user that can only SEE one database, and only select from it?
sql, sql-server, sql-server-2008, sql-server-2008-r2, t-sql
Solution
1) Create the user on the server
2) Add the user to the given database
3) Grant read-only access to the database
USE [master]
CREATE LOGIN [SomeUserName] WITH PASSWORD=N'someStr0ngP@ssword', DEFAULT_DATABASE=[c], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE [c]
CREATE USER [SomeUserName] FOR LOGIN [SomeUserName] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_datareader', N'SomeUserName'
Problem
We have several databases on SQL server. We would like to create 1 new user that can see database 'c' but can not see the rest of the databases. This user should only be able to select from this database, and nothing else. I have been googleing and searching for a while now, and the closest I found was to deny view any database, and then make them the database owner. But I don't think that will work for limiting them to select, unless is there a way I can deny everything except for select on a database owner? Thanks in advance for any help! Edit: SQL Server 2008 R2, by the way. Edit2: Sorry, I was not clear in my original post. I am looking to make it so when they log in they won't even see the names of other databases, not just that they can't access them. Thanks.