创建只能看到一个数据库的用户，并且只能从中选择? [英] Create user that can only SEE one database, and only select from it?

问题描述

我们在 SQL 服务器上有几个数据库.我们想创建 1 个可以看到数据库c"但看不到其余数据库的新用户.

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?

提前感谢您的帮助！

顺便说一句，SQL Server 2008 R2.

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.

谢谢.

推荐答案

1) 在服务器上创建用户

1) Create the user on the server

2) 将用户添加到给定的数据库中

2) Add the user to the given database

3) 授予对数据库的只读访问权限

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'

这篇关于创建只能看到一个数据库的用户，并且只能从中选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！